Add A NOT NULL Column To An Existing Table
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
Working with NOVALIDATE
Tonight on the OTN forums we have what is obviously a bunch of students trying to cheat on an exam given by their instructor. They are all asking how to add a NOT NULL column to an existing table.

My goal is to get them caught for cheating by giving them a solution they could not possibly know on their own. And since it really is a perfectly good, and elegant, solution I thought I would share it with everyone here as well.

First lets build the table and insert a couple of row.
CREATE TABLE test (
col1 NUMBER,
col2 NUMBER);

INSERT INTO test VALUES (1,1);
INSERT INTO test VALUES (2,2);


SELECT * FROM test;
Now we will add the NOT NULL constraint. First I will do it the way most people do which will fail.
ALTER TABLE test ADD (col3 NUMBER);

ALTER TABLE test MODIFY (col3 NOT NULL);

desc test

ALTER TABLE test DROP COLUMN col3;

desc test

ALTER TABLE test ADD (col3 NUMBER NOT NULL);
Now lets do it correctly
ALTER TABLE test ADD (col3 NUMBER);

ALTER TABLE test
ADD CONSTRAINT cc_test_col3_notnull
CHECK (col3 IS NOT NULL)
NOVALIDATE;

desc test

SELECT * FROM test;
Lets test the table and see if it works as expected.
INSERT INTO test VALUES (3, 3, 3);

SELECT * FROM test;

INSERT INTO test VALUES (4, 4, NULL);
The beauty of this solution is that you do not need to fill the column with that, from a historical perspective, are faked as the column in the past did not exist. In addition, the constraint has a meaningful name (not some system generated nonsense like SYS_C0036320) which makes it easier to later disable it or drop it.
 
Related Topics
Constraints
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved