The Right and Wrong Ways to Add a NOT NULL Constraint

I tested these examples in an Oracle 11.2 database. The first one shows how to add a NOT NULL constraint retrospectively. You start by creating a table:

SQL> create table andrew (col1 varchar2(1))
  2  /

Table created.

SQL>

Then at some point in the future, you add a NOT NULL constraint like this: 

SQL> alter table andrew modify (col1 not null)
  2  /

Table altered.

SQL>

Doing it this way, the constraint is obvious when you describe the table:

SQL> desc andrew
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                       NOT NULL VARCHAR2(1)

SQL>

… and, if you try to add a null value, the error message is self-explanatory:

SQL> insert into andrew values (null)
  2  /
insert into andrew values (null)
                           *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ORACLE"."ANDREW"."COL1")

SQL>

The second example, which I saw recently, shows how NOT to do it. You start, as before, by creating a table:

SQL> create table fred (col1 varchar2(1))
  2  /

Table created.

SQL>

… then you add a CHECK constraint as follows:

SQL> alter table fred
  2  add constraint con1
  3  check (col1 is not null)
  4  /

Table altered.

SQL>

You cannot see this constraint when you describe the table:

SQL> desc fred
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                                VARCHAR2(1)

SQL>

… and when you try to add a null value, the error message is not very helpful:

SQL> insert into fred values (null)
  2  /
insert into fred values (null)
*
ERROR at line 1:
ORA-02290: check constraint (ORACLE.CON1) violated

SQL>

Constraints (Part 7) - On Delete Set Null

This example was tested on Oracle 9. It creates another foreign key constraint. First create emp and dept tables as before. Note the on delete set null clause when the employee table is created. We will see what it does later: 

SQL> create table dept
  2  (dept_no  varchar2(4) unique,
  3  dept_desc varchar2(10))
  4  /

Table created.

SQL> create table emp
  2  (emp_no     varchar2(4),
  3  emp_name    varchar2(10),
  4  emp_dept_no varchar2(4)
  5  constraint fk_dept_no
  6  references dept(dept_no)
  7  on delete set null)
  8  /

Table created.

SQL>

And create a department with one employee as in earlier examples: 

SQL> insert into dept values ('D001','IT')
  2  /

1 row created.

SQL> insert into emp values ('E001','Andrew','D001')
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew     D001

SQL> 

Delete the IT department and see what happens to its one employee:

SQL> delete dept
  2  /

1 row deleted.

SQL> select * from dept
  2  /

no rows selected

SQL> 

The employee is still in the emp table but his department number has been set to null. As with the on delete cascade clause in the previous example, you need to be really sure that this is what you want to do:

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew

SQL>

Rollback the transaction, set the employee's department number to be not null, then try again:

SQL> rollback;

Rollback complete.

SQL> alter table emp modify emp_dept_no not null
  2  /

Table altered.

SQL>

As you might expect, the delete dept statement fails because the employee's department number can no longer be set to null:

SQL> delete dept
  2  /
delete dept
      *
ERROR at line 1:
ORA-01407: cannot update
("ORACLE"."EMP"."EMP_DEPT_NO") to NULL

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT

SQL> select * from emp
  2  /

EMP_ EMP_NAME   EMP_DEPT_NO
---- ---------- -----------
E001 Andrew     D001

SQL>

Constraints (Part 6) - On Delete Cascade

Go to part 5

There is more than one way to create a foreign key constraint. This example, tested on an Oracle 9 database, uses the on delete cascade clause. First create a department table:  

SQL> create table dept

  2  (dept_no  varchar2(4) unique,

  3  dept_desc varchar2(10))

  4  /

Table created.

SQL>

Next create an employee table using the on delete cascade clause. We will see what this is for later:

SQL> create table emp

  2  (emp_no     varchar2(4),

  3  emp_name    varchar2(10),

  4  emp_dept_no varchar2(4)

  5  constraint fk_dept_no

  6  references dept(dept_no)

  7  on delete cascade)

  8  /

Table created.

SQL> 

Set up an IT department with one employee: 

SQL> insert into dept values ('D001','IT')

  2  /

1 row created.

SQL> insert into emp values ('E001','Andrew','D001')

  2  /

1 row created.

SQL> select * from dept

  2  /

DEPT DEPT_DESC

---- ----------

D001 IT

SQL> select * from emp

  2  /

EMP_ EMP_NAME  EMP_DEPT_NO

---- ---------- -----------

E001 Andrew    D001

SQL>

Now delete the IT department from the dept table. The on delete cascade clause means that any employee(s) in this department will be deleted from the emp table without warning:

SQL> delete dept

  2  /

1 row deleted.

SQL> select * from dept

  2  /

no rows selected

SQL> select * from emp

  2  /

no rows selected

SQL>

Later on, i.e. when it is too late to rollback the transaction, you may decide that the IT department was removed in error. If you then reinstate it in the dept table, you will somehow need to find the names of the employees who work there and add them to the emp table again.

Constraints (Part 5) - ORA-02270, ORA-02249, ORA-02291 and ORA-02292

Go to part 4

This example, tested on an Oracle 9 database, starts to look at foreign key constraints. First create a department table. Each row has a department number and description:

SQL> create table dept
  2  (dept_no   varchar2(4),
  3   dept_desc varchar2(10))
  4  /

Table created.

SQL> 

Next, create an employee table. Each row has an employee number, name and department number. A foreign key constraint checks that the department number is already present in the department table:

SQL> create table emp
  2  (emp_no      varchar2(4),
  3  emp_name     varchar2(10),
  4  emp_dept_no  varchar2(4)
  5  constraint fk_dept_no
  6  references dept(dept_no))
  7  /
references dept(dept_no))
                *
ERROR at line 6:
ORA-02270: no matching unique or primary key for this
column-list

SQL>

That fails because the department number in the department table does not have to be unique. Add a constraint to ensure it is then try again:

SQL> alter table dept add constraint dept_no_pk
  2  unique(dept_no)
  3  /

Table altered.

SQL> create table emp
  2  (emp_no      varchar2(4),
  3  emp_name     varchar2(10),
  4  emp_dept_no  varchar2(4),
  5  constraint fk_dept_no
  6  foreign key (emp_dept_no)
  7  references dept(dept_no))
  8  /

Table created.

SQL>

So far so good. Now see what happens if you try to drop the department table. Note that both tables are empty at this point:

SQL> drop table dept
  2  /
drop table dept
          *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by
foreign keys

SQL> 

That fails too because the department table is now required to validate changes to the employee table. Try to add a row to the employee table:

SQL> insert into emp values ('E001','Andrew','D001')
  2  /
insert into emp values ('E001','Andrew','D001')
*
ERROR at line 1:
ORA-02291: integrity constraint (ORACLE.FK_DEPT_NO)
violated - parent key not found

SQL>

The insert is rejected because the department number has not been added to the department table yet. Add a couple of entries in the department table (including the one needed for the employee row above) and have another go:  

SQL> insert into dept values ('D001','IT')
  2  /

1 row created.

SQL> insert into dept values ('D002','Sales')
  2  /

1 row created.

SQL> insert into emp values ('E001','Andrew','D001')
  2  /

1 row created.

SQL>

That worked. Now try to delete rows from the department table:

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT
D002 Sales

SQL> delete dept
  2  /
delete dept
*
ERROR at line 1:
ORA-02292: integrity constraint (ORACLE.FK_DEPT_NO)
violated - child record found

SQL> select * from dept
  2  /

DEPT DEPT_DESC
---- ----------
D001 IT
D002 Sales

SQL>

That didn't work either because D001 already has one employee in the emp table. Note that D002 was not deleted either, even though it has no employees. Finally, drop both tables. If you do the emp table first, there is no problem:

SQL> drop table emp
  2  /

Table dropped.

SQL> drop table dept
  2  /

Table dropped.

SQL>

Go to part 6

Constraints (Part 1) - ORA-01400 and ORA-02290

These examples were tested on an Oracle 9 database.

Constraints are used to validate table data. You can add a constraint when you create a table:

SQL> CREATE TABLE MY_TABLE

  2  (MY_COLUMN NUMBER NOT NULL)

  3  /

Table created.

SQL>

Or you can add one to an existing table:

SQL> ALTER TABLE MY_TABLE

  2  ADD CONSTRAINT MY_CONSTRAINT

  3  CHECK (MY_COLUMN < 5)

  4  /

Table altered.

SQL>

Constraints can check INSERT statements:

 SQL> INSERT INTO MY_TABLE VALUES(1)

  2  /

1 row created.

SQL> INSERT INTO MY_TABLE VALUES(5)

  2  /

INSERT INTO MY_TABLE VALUES(5)

*

ERROR at line 1:

ORA-02290: check constraint

(ORACLE.MY_CONSTRAINT) violated

SQL> INSERT INTO MY_TABLE VALUES(2)

  2  /

1 row created.

SQL> INSERT INTO MY_TABLE VALUES(NULL)

  2  /

INSERT INTO MY_TABLE VALUES(NULL)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into

("ORACLE"."MY_TABLE"."MY_COLUMN")

SQL>

And they can also check updates:

SQL> UPDATE MY_TABLE

  2  SET MY_COLUMN = 6

  3  WHERE MY_COLUMN = 2

  4  /

UPDATE MY_TABLE

*

ERROR at line 1:

ORA-02290: check constraint

(ORACLE.MY_CONSTRAINT) violated

SQL>

When you have finished, only the valid data remains in the table:

SQL> SELECT * FROM MY_TABLE

  2  /

 MY_COLUMN

----------

         1

         2

SQL>

Go to part 2

Create View ... With Check Option

This post was tested on Oracle 11.2. It shows how to use the WITH CHECK OPTION clause of the CREATE VIEW command. The following two steps create a table and a view on a subset of that table i.e. rows where first_name begins with "A": 

SQL> create table t1
  2  (first_name varchar2(10))
  3  /
 
Table created.
 
SQL> create view v1
  2  as select first_name from t1
  3  where substr(first_name,1,1) = 'A'
  4  /
 
View created.
 
SQL>

Oracle allows you to use a view to insert rows which that view would then be unable to select. You can see this below where the view is used to insert a row for "BRIAN". This row can be selected from the table, which shows that it was successfully inserted. However, you cannot select it using the view because first_name does not begin with "A": 

SQL> insert into v1 select 'ANDREW' from dual
  2  /
 
1 row created.
 
SQL> insert into v1 select 'BRIAN' from dual
  2  /
 
1 row created.
 
SQL> select * from t1
  2  /
 
FIRST_NAME
----------
ANDREW
BRIAN
 
SQL> select * from v1
  2  /
 
FIRST_NAME
----------
ANDREW
 
SQL> 

This may not be what you want. For example, you would not expect an administrator in the IT department to be able to insert rows for the Sales department. Let's delete the rows from the table and try something different:

SQL> delete t1
  2  /

2 rows deleted.

SQL>

This time the WITH CHECK OPTION clause is added at the end of the CREATE VIEW statement:

SQL> create or replace view v1
  2  as select first_name from t1
  3  where substr(first_name,1,1) = 'A'
  4  with check option
  5  /

View created.

SQL>

This creates a constraint on the view, not the table. It has a system generated name:

SQL> select constraint_name
  2  from dba_constraints
  3  where table_name = 'V1'
  4  /
 
CONSTRAINT_NAME
------------------------------
SYS_C00121817
 
SQL>

With the constraint in place, you can still use the view to insert a first_name which begins with "A":

SQL> insert into v1 select 'ARTHUR' from dual
  2  /
 
1 row created.
 
SQL>

But if you try to use it to insert a first_name beginning with "B", the value is rejected:

SQL> insert into v1 select 'BARRY' from dual
  2  /
insert into v1 select 'BARRY' from dual
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
 
SQL>

... and only the valid value remains in the table:

SQL> select * from t1
  2  /
 
FIRST_NAME
----------
ARTHUR
 
SQL> select * from v1
  2  /
 
FIRST_NAME
----------
ARTHUR
 
SQL>

If you do not like the system generated name, you can choose a name for the constraint yourself like this:

SQL> create or replace view v1
  2  as select first_name from t1
  3  where substr(first_name,1,1) = 'A'
  4  with check option constraint c1
  5  /

View created.

SQL> select constraint_name
  2  from dba_constraints
  3  where table_name = 'V1'
  4  /

CONSTRAINT_NAME
------------------------------
C1

SQL>