Adding a column to a table is quite simple. We use the ALTER TABLE <table_Name> ADD command. This allows us to specify datatype, default value, etc.
However, it gets slightly more complicated if we want to add a NOT NULL column to a table that already contains data. The reason for this is quite straightforward. The NOT NULL constraint requires each row in the table to contain a value but the newly added column does not contain a value.
The work around is a three stage process:
[list=1]
[*] Add the column as an
optional column.
[*] Update the column with the desired value(s).
[*] Add a NOT NULL constraint to the column.
[list]
Note that this does not apply if you have specified a DEFAULT for the column.
The following code example demonstrates this process:
SQL> CREATE TABLE t1 (col1 NUMBER NOT NULL)
2 /
Table created.
SQL> INSERT INTO t1 VALUES (1)
2 /
1 row created.
SQL> ALTER TABLE t1 ADD (col2 NUMBER NOT NULL)
2 /
ALTER TABLE t1 ADD (col2 NUMBER NOT NULL)
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
SQL> ALTER TABLE t1 ADD (col2 NUMBER)
2 /
Table altered.
SQL> UPDATE t1
2 SET col2 = 1
3 WHERE col2 IS NULL
4 /
1 row updated.
SQL> ALTER TABLE t1 MODIFY (col2 NOT NULL)
2 /
Table altered.
SQL> ALTER TABLE t1 ADD (col3 NUMBER DEFAULT 0 NOT NULL)
2 /
Table altered.
SQL> SELECT *
2 FROM t1
3 /
COL1 COL2 COL3
---------- ---------- ----------
1 1 0
SQL>
Cheers, APC