ORACLENERD: A NULL Observation, III

A NULL Observation, III

Part I here.

Part II here.

OK, we have a winner. Coskan Gundogar suggested in the comments, that using the MODIFY clause of the ALTER TABLE statement should work. Let's see:

desc t_null

Name                    Null?    Type
----------------------- -------- ----------------
COL1                             VARCHAR2(30)

CJUSTICE@TESTING>ALTER TABLE T_NULL DROP CONSTRAINT nn_col1_tn;

Table altered.

CJUSTICE@TESTING>ALTER TABLE t_null MODIFY ( col1 CONSTRAINT nn_col1_tn NOT NULL );

Table altered.

CJUSTICE@TESTING>@DESC T_NULL
Name                    Null?    Type
----------------------- -------- ----------------
COL1                    NOT NULL VARCHAR2(30)

Voila!

Much better. The question still remains as to what's going on in the background. Until now, I had never thought that white space mattered in anything Oracle. Very strange.

Maybe I can get Miladin to dig through the internals and see what's really going on.

Labels: database, null, oracle

¶ 6/25/2009 11:03:00 PM

Comments:

@chet @oraclenerd

You may want to check the value of SYS.COL$.NULL$ when adding the NOT NULL constraint versus a CHECK constraint checking for NULL.

My guess is that when you use the NOT NULL, it gets set to 0 (or maybe it is 1). Adding a check constraint , i doubt Oracle is checking this condition and setting the flag.

# posted by

Bradd Piontek : June 26, 2009 11:50 AM