The Constraint Optimization series:
- Part 1: Optimization of Check Constraint Creation
- Part 2: Optimization that Violates Data Integrity
- Part 3: Optimization of Foreign Key Constraint Creation
- Part 4: (Lack of) Optimization of Unique Constraint Creation
- Part 5: Adding a Column with a Default Value and a Constraint
In the previous post I showed that Oracle does a nice optimization when we add a new (nullable with no default value) column with an inline check constraint in a single ALTER TABLE statement.
However, there is one case where this optimization allows for data integrity violation instead of forbidding it (which makes it a bug, in this specific case, rather than an optimization). It happens when the check constraint is “column IS NOT NULL”.
I’ll setup a table with a few records for demonstrating this:
SQL> create table t (x number); Table created. SQL> insert into t select rownum from dual connect by level<=10; 10 rows created. SQL> commit; Commit complete.
First, let's try to add a column with an out-of-line constraint, so the optimization will not apply:
SQL> SQL> ALTER TABLE T ADD ( 2 C NUMBER, 3 CONSTRAINT C_CHK CHECK (C IS NOT NULL) 4 ); CONSTRAINT C_CHK CHECK (C IS NOT NULL) * ERROR at line 3: ORA-02293: cannot validate (TRANZMATE_PRODUCTION.C_CHK) - check constraint violated
It failed, and that's good.
Now, let's try to add the same column with the same check constraint, but this time we'll use an inline constraint:
SQL> ALTER TABLE T ADD ( 2 C NUMBER CONSTRAINT C_CHK CHECK (C IS NOT NULL) 3 ); Table altered.
And it succeeded, although it should have failed.
So now we have a validated constraint, stating that C IS NOT NULL, and at the same time the table contains records, and in all of them C IS NULL:
SQL> select table_name,search_condition,status,validated,rely 2 from user_constraints where constraint_name='C_CHK'; TABLE_NAME SEARCH_CONDITION STATUS VALIDATED RELY ---------- -------------------- ---------- ---------- ------------ T C IS NOT NULL ENABLED VALIDATED SQL> select * from t; X C ---------- ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected.
Since we're talking about adding a nullable column with no default value to an existing table that already contains rows, specifying a check constraint that states that this column is not nullable doesn't make much sense, and if someone tries to do it it's probably their bug. Still, it would be nice to get an error in this case, rather than violating the data integrity.
Why does it happen?
When Oracle validates some check constraint, it checks if there are records for which the constraint condition is FALSE. When the column is NULL, then usually the condition evaluates to NULL, not to FALSE, so the constraint is valid. For example, when C is NULL, then "C>0" is NULL (neither FALSE nor TRUE).
I guess this is why Oracle skips the validation step completely as part of this optimization.
What should happen?
It's good that Oracle doesn't validate each and every record, as it's really unnecessary, but to make this optimization foolproof it should validate a single value – NULL.