NOT NULL vs. Check Constraints

I found Jonathan Lewis's excellent article about "incorrectly" defining NOT NULL columns some time ago.  You can read his article at http://jonathanlewis.wordpress.com/2010/09/05/not-null/

Having read it, I did some investigation into our production databases and, lo and behold, found some examples of columns with NOT NULL check constraints but without the NOT NULL column definition.

Here's the query I used to find the 'guilty' columns:

 
select o.owner, o.object_type, o.object_name, t.column_name, co.search_condition, co.validated
 from dba_objects o, sys.col$ c, dba_tab_columns t, dba_cons_columns cc, dba_constraints co
 where o.owner = '&table_owner'
 and o.object_type = 'TABLE'
 and o.object_id = c.obj#
 and o.owner = t.owner
 and o.object_name = t.table_name
 and t.column_name = c.name
 and cc.owner = o.owner
 and cc.table_name = t.table_name
 and cc.column_name = c.name
 and co.owner = cc.owner
 and co.constraint_name = cc.constraint_name
 and co.constraint_type = 'C'
 and c.null$ = 0
order by object_name, column_name; 

Note that in 10g and 11g the DBA_CONSTRAINTS.SEARCH_CONDITION column is stored as a LONG.  Which means that you can't filter out NOT NULL check conditions (e.g. "column_name in (0,1)") from any other check conditions in the query.

Deprecated for at least 15 years, but still used in the data dictionary.  Thanks Oracle!

The alternative is to make your own one-off copy of DBA_CONSTRAINTS, using TO_LOB() to convert SEARCH_CONDITION to a CLOB.  Then the query can be re-written as:

 
select o.owner, o.object_type, o.object_name, t.column_name, co.search_condition, co.validated
 from dba_objects o, sys.col$ c, dba_tab_columns t, dba_cons_columns cc, my_dba_constraints co
 where o.owner = '&table_owner'
 and o.object_type = 'TABLE'
 and o.object_id = c.obj#
 and o.owner = t.owner
 and o.object_name = t.table_name
 and t.column_name = c.name
 and cc.owner = o.owner
 and cc.table_name = t.table_name
 and cc.column_name = c.name
 and co.owner = cc.owner
 and co.constraint_name = cc.constraint_name
 and co.constraint_type = 'C'
 and c.null$ = 0
 and co.search_condition not like '% AND %'
 and co.search_condition not like '% OR %'
 and co.search_condition like '%NOT NULL%'
order by object_name, column_name;