How can I store NULL in a NOT NULL field?

Question

I just met NULL values ​​in NOT-NULL fields in our test database. How could they get there? I know that NOT-NULL constraints can be changed using the NOVALIDATE clause, but this will change the last_ddl_time table to USER_OBJECTS. And this time is less than the date of creation of these records. Is there anything else I'm missing? Or does someone manually work for sure?

The table is divided and organized by index, if relevant. Oracle Version - 9.2

Answer 1

A column condition is NOT NULLnot like other restrictions: you can disable the NOT NULL constraint, but the column will not be counted NOT NULLif you re-enable the constraint using 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 restriction and reconnect it using NOVALIDATE, the column will not 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 the NOT NULLABLE column (as per my last request), you have an error (contact support?)

Answer 2

Check If Suspended Restriction / Disabled

Answer 3

, ? :

SELECT field
  FROM your_table
 WHERE not_null_field IS NULL;

? , ...