The pain of oracle nulls in check constraints

So, you are using oracle and think you have the part about ” strings beeing null nailed?

Well, so did I (only goes to show how silly I am) until I found that a number of constraints was not working at all. Example

drop table testme;
create table testme (
z NVARCHAR2(13),
a NVARCHAR2(13) CHECK (a IN ( 'foo', 'bar' ) ),
b NVARCHAR2(13) CHECK (b IN ( '', 'bar' ) )
);

insert into testme values ( '1', '', '' ); -- OK, a allowed to be null
insert into testme values ( '2', 'foo', '' ); -- OK, b allowed to ''/null
insert into testme values ( '3', '', 'bar' ); -- OK, b allowed to be bar
insert into testme values ( '4', 'foo', 'bar' ); -- OK
insert into testme values ( '5', 'foobar', 'bar' ); -- Fails: a not allowed to be foobar: OK
insert into testme values ( '6', 'foo', 'foo' ); -- DOES NOT FAIL??? b not allowed to be foo?

The reason the check on b does not work, is because of three things:

a) ” becomes null in oracle (but you knew that, because you learn that very early on)

b) comparing something to null becomes unknown in oracle (I did not*really* appreciate this)

c) CHECK only fails, if the result is actually False. (I did not at all know this)

c) means that CHECK does not fail, if the result is unknown…

Enter Wikipedia:

“While a DML WHERE clause must evaluate to True for a row, a check
constraint must not evaluate to False. This means that a check
constraint will succeed if the result of the check is either True or
Unknown.”

http://en.wikipedia.org/wiki/Null_(SQL)#Check_constraints

So, the check does not fail.

Now, if you tried the same in statement, with a select, it would not actually return any rows. Assume the table contains a row where b = ‘foobar’

select * from testme where b in ( '', 'bar )

As you would expect, this only returns rows where b == ‘bar’.

…

Ok, so I know this now. I still believe the engineer that thought up this particular piece of sematics should be hugely ashamed…

Advertisement