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>

ORA-01442

This was tested on Oracle 11.2. If you define a column as NOT NULL then try to modify it to NOT NULL again, you get an ORA-01442:

SQL> create table tab1 (col1 number not null)

  2  /

 

Table created.

 

SQL> alter table tab1 modify (col1 number not null)

  2  /

alter table tab1 modify (col1 number not null)

                         *

ERROR at line 1:

ORA-01442: column to be modified to NOT NULL is

already NOT NULL

 

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>

ORA-01758 and ORA-12988

I read that you could not add a NOT NULL column to a table which already contained rows. It was not something I had ever thought about but it seemed reasonable. If you were allowed to do it, the table would end up with NULLS in a NOT NULL column after you had added the column. I decided to give it a try on Oracle 11.1.0.6.0 running on Windows XP. First I created a table:

SQL> create table people
  2  (first_name varchar2(10))
  3  /

Table created.

SQL>

Then I added a NOT NULL column while the table was still empty. This worked OK:

SQL> alter table people add
  2  (age number not null)
  3  /

Table altered.

SQL>

Next I tried to drop the column but got an ORA-12988 as I was logged in as SYS:

SQL> alter table people
  2  drop column age
  3  /
alter table people
*
ERROR at line 1:
ORA-12988: cannot drop column from table owned by SYS

SQL>

I dropped the table instead and recreated it:

SQL> drop table people
  2  /

Table dropped.

SQL> create table people
  2  (first_name varchar2(10))
  3  /

Table created.

SQL>

Then I inserted a row and tried to add a NOT NULL column. This failed with an ORA-01758:

SQL> insert into people values ('ANDREW')
  2  /

1 row created.

SQL> alter table people add
  2  (age number not null)
  3  /
alter table people add
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

SQL>

There are a few ways round this:

(1) You could empty the table, add the NOT NULL column then reinsert the data. I don’t think much of that idea.
(2) You could add the column without the NOT NULL constraint, put a value in the column in every row then add the NOT NULL constraint. I think this is the best suggestion.
(3) You could include a DEFAULT value as shown below then update it afterwards if necessary:

SQL> l
  1  alter table people add
  2* (age number default 50 not null)
SQL> /

Table altered.

SQL> select * from people
  2  /

FIRST_NAME        AGE
---------- ----------
ANDREW             50

SQL>

Calculating Averages (Part 2)

Go to part 1

(Tested on Oracle 9 but probably not on the database used for part 1.)

In Calculating Averages (Part 1) I ran the following SQL:

select avg(num_rows) from dba_tables

where num_rows is not null;

Since then I have read that the where clause was unnecessary as Oracle does not include null values when it works out averages. I decided to see if this was true. First I checked that there were both null and not null values in the num_rows column in dba_tables:

SQL> select count(*) from dba_tables

  2  where num_rows is null;

  COUNT(*)

----------

       459

SQL> select count(*) from dba_tables

  2  where num_rows is not null;

  COUNT(*)

----------

       366

SQL>

Incidentally, you can combine the two statements above into one by using decode:

SQL> select

  2  decode(num_rows,null,'NULL','NOT NULL') row_count,

  3  count(*)

  4  from dba_tables

  5  group by decode(num_rows,null,'NULL','NOT NULL');

ROW_COUNT   COUNT(*)

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

NOT NULL         366

NULL             459

SQL>

Then I calculated the average as before:

SQL> select avg(num_rows) from dba_tables

  2  where num_rows is not null;

AVG(NUM_ROWS)

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

   783867.724

SQL>

Finally, I omitted the where clause and the result was the same. This proves that avg ignores null values:

SQL> select avg(num_rows) from dba_tables;

AVG(NUM_ROWS)

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

   783867.724

SQL>

Calculating Averages (Part 1)

You can calculate average values with the AVG function as shown below. The second statement double checks the answer:

SQL> select avg(num_rows)

  2  from dba_tables

  3  where num_rows is not null

  4  /

AVG(NUM_ROWS)

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

   153051.912

SQL> select sum(num_rows) / count(*)

  2  from dba_tables

  3  where num_rows is not null

  4  /

SUM(NUM_ROWS)/COUNT(*)

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

            153051.912

SQL>

Footnote: Since writing this post I have noticed that it is slightly misleading.
Go to part 2 for details.