Null – again

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