Q

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.

This was first published in January 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close