Here’s a (camouflaged) constraint definition I came across a little while ago in a production system:
create table t1 ( v1 varchar2(10), constraint c1 check (v1 = 'A' or v1 = null) );
Quick question – will the following insert statement work or return an error ?
insert into t1 values('B');
The answer is that the row gets inserted – and that’s probably not an intended result.
It’s amazing how often NULL rears its ugly head as the cause of unexpected behaviour. Remember that a constraint accepts a value if the constraint does not evaluate to false. If you put ‘B’ into the constraint definition above, we get:
'B' = 'A' or 'B' = null hence FALSE or NULL hence NULL
The constraint does not evaluate to FALSE, the row is inserted.
Apart from the possibility of unexpected data, there is a secondary problem with this constraint. Given that the programmer has created at least one constraint that indicates that he (or she) has explicitly tried to make NULL a legal option, does this mean that he (or she) thinks that every other constraint which doesn’t have a “col = null” check is implicitly going to stop NULLs appearing ? (Or, to say the same thing a little differently, did the programmer think that without this extra test the constraint would otherwise block NULLs).
Remember, if you want to block nulls in a column you should either declare the column to be NOT NULL, or include a constraint of the form “column is not null”.
Finally there’s one more problem – is this the only check constraint with this “= null” error, or are there a few more scattered around the system. So it’s time to search the data dictionary to find the rest (if there are any).