SQL> desc x Name Null?
SQL*Plus shows the column as being nullable. But if we attempt to insert into table “X” we get: SQL> INSERT INTO x VALUES (NULL, NULL); INSERT INTO x VALUES (NULL, NULL) * ERROR at line 1: ORA-02290: check constraint (TEST.X_C01) violated SQL> So we have SQL*Plus [correctly] showing the column as being nullable, but when we attempt to insert NULLs into column “X”.”A” we get a constraint violation. Note however that it is a CHECK constraint and not a NOT-NULL constraint. • A foreign key is defined in the database with a delete rule of SET NULL but is shown in the schema report generated by TOYS (and generated DDL scripts) as having a delete rule of NO ACTION. Is this a bug? This particular problem is a bug in the USER_CONSTRAINTS catalog view (also DBA_ and ALL_) in Oracle 8.0 and 8i. This bug defines the delete rule as CASCADE or NO-ACTION. That is, the SET-NULL action was not decoded. This has been fixed in 9i. If you have the privilege SELECT ANY TABLE when capturing the schema meta-
SQL*Plus shows the column as being nullable. But if we attempt to insert into table “X” we get: SQL> INSERT INTO x VALUES (NULL, NULL); INSERT INTO x VALUES (NULL, NULL) * ERROR at line 1: ORA-02290: check constraint (TEST.X_C01) violated SQL> So we have SQL*Plus [correctly] showing the column as being nullable, but when we attempt to insert NULLs into column “X”.”A” we get a constraint violation. Note however that it is a CHECK constraint and not a NOT-NULL constraint. • A foreign key is defined in the database with a delete rule of SET NULL but is shown in the schema report generated by TOYS (and generated DDL scripts) as having a delete rule of NO ACTION. Is this a bug? This particular problem is a bug in the USER_CONSTRAINTS catalog view (also DBA_ and ALL_) in Oracle 8.0 and 8i. This bug defines the delete rule as CASCADE or NO-ACTION. That is, the SET-NULL action was not decoded. This has been fixed in 9i. If you have the privilege SELECT ANY TABLE when capturing the schema meta-
