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>