Question

I just came across NULL values in NOT-NULL fields in our test database. How could they get there? I know that NOT-NULL constraints can be altered with NOVALIDATE clause, but that would change table s last_ddl_time in USER_OBJECTS. And that time is less than the date that those records were created. Is there something else I m overlooking? Or is that someone s manual work for sure?

Table is partitioned and index-organized if that is relevant. Oracle version is 9.2

Answer 1

The NOT NULL column condition is not like other constraints: you can disable a NOT NULL constraint but the column won t be considered NOT NULL if you reenable the constraint with NOVALIDATE. Let s build a small example:

SQL> CREATE TABLE tt (ID NUMBER NOT NULL);

Table created

SQL> SELECT column_name, nullable FROM user_tab_columns WHERE table_name =  TT ;

COLUMN_NAME                    NULLABLE
------------------------------ --------
ID                             N

now if I disable the constraint and reenable it with NOVALIDATE, the column won t be considered NOT NULLABLE by Oracle:

SQL> SELECT constraint_name, search_condition
  2    FROM user_constraints WHERE table_name =  TT ;

CONSTRAINT_NAME                SEARCH_CONDITION
------------------------------ ----------------------------
SYS_C00786538                  "ID" IS NOT NULL

SQL> ALTER TABLE tt MODIFY CONSTRAINT SYS_C00786538 DISABLE;

Table altered

SQL> ALTER TABLE tt MODIFY CONSTRAINT SYS_C00786538 ENABLE NOVALIDATE;

Table altered

SQL> SELECT column_name, nullable FROM user_tab_columns WHERE table_name =  TT ;

COLUMN_NAME                    NULLABLE
------------------------------ --------
ID                             Y

So, I would say that if you have NULL values in a NOT NULLABLE column (as per my last query) you have a bug (contact support?)

Answer 2

Check If the constraint are suspended / Disabled

Answer 3

And you re sure these columns are really null? In other words:

SELECT field
  FROM your_table
 WHERE not_null_field IS NULL;

returns rows? Perhaps they ve just got non-displayable data...

友情链接