Problem solve Get help with specific problems with your technologies, process and projects.

Adding a new NOT NULL field to an existing table

What needs to be taken care of if I want to add a new field with a NOT NULL to an existing table that has so many, or n number of records? What will be the impact? Can I do this?

Let's look at the following example:

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> alter table emp add nulltest varchar2(5) not null ;
alter table emp add nulltest varchar2(5) not null
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

There's your answer.... if a table already has data you cannot add a new field with a NOT NULL constraint.

If what you're trying to accomplish is to make sure that you never have null values in a column, then try something like this:

SQL> alter table emp add (nulltest varchar2(5) default 'XXXXX') ;

The default clause will force the column to have a value if no value is given to the column when it is inserted. This keeps out the unwanted nulls and also has the benefit of making index usage work more optimally.

Another way to accomplish creating a column with NOT NULL would be to create a duplicate table using CREATE TABLE AS syntax. Then truncate the original table, add the new column with the NOT NULL constraint and then use INSERT INTO...SELECT to put your data back in. You could also do this with exp/imp utilities as well.

Dig Deeper on Oracle and SQL