How can I store NULLs in NOT NULL field?

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

Check If the constraint are suspended / Disabled

Answer 2

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

Answer 3

Hi jva,

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?)