Oracle Scratchpad

September 6, 2012

Null – again

Filed under: NULL,Oracle — Jonathan Lewis @ 5:41 pm BST Sep 6,2012

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

5 Comments »

  1. This is one of those logic bugs that I might look at several times and not notice the error.
    Of course it is not necessary to include the check for null in the constraint anyway, just keep the column nullable.

    Comment by Jared — September 6, 2012 @ 6:02 pm BST Sep 6,2012 | Reply

  2. create table t1 (
    	v1 varchar2(10),
    	constraint c1 check (v1 = 'A' or v1 is null)
    );
    

    With this insert into t1 values(‘B’); would be prevented…

    Comment by Sasa Petkovic — September 7, 2012 @ 7:55 am BST Sep 7,2012 | Reply

  3. Sasa, what is the purpose of including ‘is null’ in the constraint?

    Comment by Jared — September 7, 2012 @ 2:47 pm BST Sep 7,2012 | Reply

    • Jared,

      I nearly asked a similar question – then realised that I hadn’t made any explicit comment to the effect that comparisons with null should be “is NULL” rather than “= NULL”, so I suspect that Sasa’s comment was intended to make that point for me (even though it’s redundant in the example).

      Comment by Jonathan Lewis — September 7, 2012 @ 7:07 pm BST Sep 7,2012 | Reply

      • Thanks for your comment Jonathan. I considered that as well, but thought my question might bring some clarity to folks that may be reading this and have only recently started learning about oracle. The astute newbies will create and test the table with the various methods. :)

        Comment by Jared — September 7, 2012 @ 7:24 pm BST Sep 7,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,530 other followers