|
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. |
