Oracle Scratchpad

February 21, 2011

Constraints

Filed under: Infrastructure,NULL,Oracle,Troubleshooting — Jonathan Lewis @ 7:15 pm BST Feb 21,2011

There’s an important detail about constraints – check constraints in particular – that’s easy to forget, and likely to lead you into errors. Here’s a little cut-n-paste demo from an SQL*Plus session:

SQL> select count(*) from t1;

  COUNT(*)
----------
         4

1 row selected.

SQL>
SQL> alter table t1 add constraint t1_ck_v1 check (v1 in ('a','b','c'));

Table altered.

SQL>
SQL> select count(*) from t1 where v1 in ('a','b','c');

  COUNT(*)
----------
         3

1 row selected.

We count the number of rows in a table – and it’s four.
We add a constraint to restrict the values for a certain column – and every column survives the check.
We use the corresponding predicate to count the number of rows that match the check constraint – and we’ve lost a row !

Why ?

A predicate returns a row if it evaluates to TRUE.
A constraint allows a row if it does not evaluate to FALSE - which means it is allowed to evaluate to TRUE or to NULL.

I have one row in the table where v1 is null, and for that row the check constraint evaluates to NULL, which is not FALSE. So the row passes the check constraint, but doesn’t get returned by the predicate. I think it’s worth mentioning this difference because from time to time I see production systems that “lose” data because of this oversight.

To make the constraint consistent with the predicate I would probably add a NOT NULL declaration to the column or rewrite the constraint as (v1 is not null and v1 in (‘a’,’b’,’c’)).

21 Comments »

  1. Excellent reminder! Thanks, JL

    Comment by Mark W. Farnham — February 21, 2011 @ 10:20 pm BST Feb 21,2011 | Reply

  2. > To make the constraint consistent with the predicate I would probably add a NOT NULL declaration to the column or rewrite the constraint as (v1 is not null and v1 in (‘a’,’b’,’c’))

    I think it is worth to add that it makes a difference to the optimizer whether a column is defined as explicitly NOT NULL or if a check constraint is added that enforces the “column IS NOT NULL”. The latter won’t be recognized by the optimizer for possible query transformations whereas the former does. So I think as a general advice one should always prefer the explicit NOT NULL column definition rather than the check constraint form of the same logical constraint.

    By the way, I find this behaviour questionable, but it seems to be like that in all current releases.

    Randolf

    Comment by Randolf Geist — February 21, 2011 @ 10:27 pm BST Feb 21,2011 | Reply

    • I think it is worth to add that it makes a difference to the optimizer whether a column is defined as explicitly NOT NULL or if a check constraint is added that enforces the “column IS NOT NULL”. The latter won’t be recognized by the optimizer for possible query transformations whereas the former does. So I think as a general advice one should always prefer the explicit NOT NULL column definition rather than the check constraint form of the same logical constraint.

      absolutely, agreed, COLS.NULLABLE is only set to ‘N’ when you specify a not null constraint

      By the way, I find this behaviour questionable, but it seems to be like that in all current releases.

      given that there are unfinitely many ways to formulate a check constraint that is logically equivalent to “column is not null”, this behaviour does not seem to be questionable at all.

      See:

      sql > create table t ( a int constraint a_nn check(a is not null), b int constraint b_nn check(coalesce(b, 1) = coalesce(b,2)), c int not null);
      
      Table created.
      
      sql > select column_name, nullable from user_tab_columns where table_name='T' and column_name=any('A', 'B', 'C');
      
      COLUMN_NAME		       NUL
      ------------------------------ ---
      A			       Y
      B			       Y
      C			       N
      
      sql > insert into t(a,b,c) values(1,null,1);
      insert into t(a,b,c) values(1,null,1)
      *
      ERROR at line 1:
      ORA-02290: check constraint (SOKRATES.B_NN) violated
      
      

      So, you think, NULLABLE should be ‘F’ for all columns ‘A’, ‘B’ and ‘C’ ?
      Does not make much sense, I think.

      Comment by Sokrates — February 22, 2011 @ 9:34 am BST Feb 22,2011 | Reply

      • > given that there are unfinitely many ways to formulate a check constraint that is logically equivalent to “column is not null”, this behaviour does not seem to be questionable at all.

        That’s a fair point. However, if you look what Oracle generates if you define a column as NOT NULL, you’ll notice that it actually adds a “COLUMN IS NOT NULL” implicit check constraint… So my point is – if Oracle seems to suggest that setting a column to NOT NULL adds an implicit “COLUMN IS NOT NULL” constraint, why is an explicitly created check constraint stating exactly the same leading to a different behaviour?

        Try this:

        create table t (col1 number not null, col2 number check (“COL2″ IS NOT NULL));

        and then check the contents of USER_CONSTRAINTS for table T.

        So I guess I should have been more specific in what I actually find questionable – your point about the infinite ways to define a column as mandatory is well taken and I certainly didn’t want to suggest that Oracle should evaluate that, but a simple IS NOT NULL check constraint is something that seems to be feasible to me, in particular since Oracle adds exactly the same check constraint implicitly.

        Of course it is arguable that this “simple” constraint could be combined with further non-trivial checks complicating the matter again, but then I would prefer that Oracle didn’t generate that implicit check constraint when defining a column as NOT NULL to make the point obvious that these two things are not the same.

        Randolf

        Comment by Randolf Geist — February 22, 2011 @ 10:25 am BST Feb 22,2011 | Reply

        • I think the point is, that a
          NOT NULL Integrity Constraint

          http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datainte.htm#CNCPT1641

          is just another beast than a
          Check Constraint

          http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datainte.htm#CNCPT1660

          So, if you want a NOT NULL Integrity Constraint – and you pointed out that it may make a difference to the optimizer (though you didn’t gave an example for this) – why not explicitly specifying it ?

          (instead of relying on Oracle to implicitly convert a check constraint into a NOT NULL Integrity Constraint without telling it it should do so)

          Comment by Sokrates — February 22, 2011 @ 11:22 am BST Feb 22,2011

        • May be I still have expressed myself badly… I’ve already tried to clarify what in particular I find questionable: I only say that Oracle adds itself an implicit CHECK CONSTRAINT when defining a column as NOT NULL suggesting that it ought to have the same effect, but it doesn’t.

          As you say, if we agree that at present NOT NULL and CHECK constraints are two different things then Oracle at least should not add the implicit IS NOT NULL check constraint – that is my point.

          On the other hand – if Oracle suggests they have the same effect by adding a corresponding check constraint then I think it is reasonable to imply that Oracle indeed can evaluate the check constraint accordingly (see Jonathan’s point of view below)

          Randolf

          Comment by Randolf Geist — February 22, 2011 @ 1:00 pm BST Feb 22,2011

        • Sokrates,

          regarding the example of the constraint making a difference to the optimizer you ask for, there are more subtle cases than the obvious index usage case mentioned by Jonathan. I was referring to query transformations, and here is one such example (my comments apply to 10.2.0.4/5 for example, but probably for all other versions as well):

          set echo on pagesize 0 linesize 130 tab off
          
          drop table t1 purge;
          
          drop table t2 purge;
          
          create table t1 (col1 number not null, col2 number check ("COL2" IS NOT NULL));
          
          create table t2 (col1 number not null);
          
          -- Anti-join transformation possible
          explain plan for
          select * from t2 where col1 not in (select col1 from t1);
          
          select * from table(dbms_xplan.display);
          
          -- Anti-join transformation not possible
          explain plan for
          select * from t2 where col1 not in (select col2 from t1);
          
          select * from table(dbms_xplan.display);
          
          -- Preventing the transformation
          explain plan for
          select * from t2 where col1 not in (select /*+ no_unnest */ col1 from t1);
          
          select * from table(dbms_xplan.display);
          
          -- But can not force the transformation
          explain plan for
          select * from t2 where col1 not in (select /*+ unnest */ col2 from t1);
          
          select * from table(dbms_xplan.display);
          
          -- The transformation is only possible when specifying obvious "duplicate" information
          explain plan for
          select * from t2 where col1 not in (select col2 from t1 where col2 is not null);
          
          select * from table(dbms_xplan.display);
          

          Randolf

          Comment by Randolf Geist — February 22, 2011 @ 1:20 pm BST Feb 22,2011

  3. I should have learned this sometime in the past and managed to forget it, thanks for the reminder.

    Comment by Bernard Polarski — February 22, 2011 @ 9:06 am BST Feb 22,2011 | Reply

  4. well documented:

    http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datainte.htm#CNCPT1660

    “.. A check constraint on a column or set of columns requires that a specified condition be true or unknown for every row …”

    Comment by Sokrates — February 22, 2011 @ 9:27 am BST Feb 22,2011 | Reply

  5. Hmm. ” a specified condition be true or unknown for every row …”
    Does it say when (under what conditions) can it / will it be UNKNOWN ?

    Comment by Hemant K Chitale — February 22, 2011 @ 9:52 am BST Feb 22,2011 | Reply

  6. Sokrates,

    A NOT NULL constraint is logically exactly the same thing as a “column is not null” check constraint – they both constrain the data in exactly the same way. The mechanical implementation may differ, but that shouldn’t affect the logic.

    Your argument that there are lots of different ways to create a check constraint that is logically equivalent to “column is not null” is irrelevant – if the optimizer were good enough it would be able to infer the “column is not null” condition from any logical equivalent, and ought to optimise accordingly.

    The optimizer already tries to transform predicates and constraints to optimise (or sometimes “pessimse”) the SQL – and that’s the primary reason why the (very obvious and totally explicit) “column is not null” constraint should, arguably, be usable in exactly the same way as the NOT NULL constraint. (I’d guess that the particular case that Randolf had in mind was probably the ability of the optimizer to recognise an index-only query when you use NOT NULL, but not to use the index when you use “column is not null”).

    The implementation defect (from a purist point of view, rather than a pragmatic point of view) is that Oracle implements a NULLABLE flag on a column – it’s a hangover from the early versions and should have been retired when check constraints appeared.

    Comment by Jonathan Lewis — February 22, 2011 @ 12:31 pm BST Feb 22,2011 | Reply

    • …if the optimizer were good enough it would be able to infer the “column is not null” condition from any logical equivalent, and ought to optimise accordingly…

      I would conjecture – though I cannot prove it – that this ( “given an SQL-Expression expr(column), is it logically equivalent to ‘column is not null’ ?”) is as hard as the tautology problem (“given a Boolean formula, is it equivalent to ‘0=0′ ?”), which is known to be NP-hard

      Comment by Sokrates — February 22, 2011 @ 1:27 pm BST Feb 22,2011 | Reply

      • Okay, let me modify that:


        “…if the optimizer were good enough it would be able to infer the “column is not null” condition from any logical equivalent that hadn’t been written by a mathematician with a warped sense of humour, and ought to optimise accordingly”
        ;)

        I think your conjecture seems reasonable – an intuitive view (not that I like to trust my intuition) would be that the constraint problem is the tautology problem extended to three-valued logic, and the tautology problem couldn’t be NP-hard if the constraint problem weren’t.

        The fact that some reductions to “is not null” may not be feasible doesn’t mean that an explicit “is not null” should be treated differently from a NOT NULL check, though.

        Comment by Jonathan Lewis — February 23, 2011 @ 10:54 am BST Feb 23,2011 | Reply

        • The fact that some reductions to “is not null” may not be feasible doesn’t mean that an explicit “is not null” should be treated differently from a NOT NULL check, though.

          ok, agree – like many others
          see for example

          http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2665514800346181577#2666924900346321646

          (has someone filed an enhancement request ?)

          Comment by Sokrates — February 23, 2011 @ 12:15 pm BST Feb 23,2011

    • Is it really a purist defect? The difference is in the Check Constraint link cited above; the check constraint is done after the statement runs, as a rollback. Perhaps it isn’t logically inconsistent to disable the integrity constraint, see if a transaction works, then roll it back with the check constraint. Whether Oracle can actually do that I haven’t considered.

      Comment by joel garry — February 23, 2011 @ 10:30 pm BST Feb 23,2011 | Reply

      • Interesting point – but arguably this does more to enforce the idea that the NOT NULL constraint is a special case that is inconsistent in more ways than one with all other uses of constraints.

        So the purist may say it shouldn’t happen, while the pragmatist might suggest that more cases should take advantage of the optimisation. (Why am I generating undo and redo for a row when I have a constraint that tells me it’s going to be rejected ?)

        Comment by Jonathan Lewis — February 24, 2011 @ 8:01 am BST Feb 24,2011 | Reply

  7. The same thing can be said about the Foreign key integrity constraint

    http://jonathanlewis.wordpress.com/2009/05/03/foreign-keys/#more-1101

    Best Regards

    Mohamed Houri

    Comment by Houri Mohamed — February 22, 2011 @ 2:38 pm BST Feb 22,2011 | Reply

  8. [...] Jonathan Lewis reminds about a subtle difference between a predicate and the constraint. [...]

    Pingback by Log Buffer #214, A Carnival of the Vanities for DBAs | The Pythian Blog — February 28, 2011 @ 2:04 pm BST Feb 28,2011 | Reply

  9. [...] As a follow on from this: a predicate applied to a dataset will return rows only when it evaluates to TRUE; a constraint will allow rows to be inserted into a table if if doesn’t evaluate to FALSE. [...]

    Pingback by NULL « Oracle Scratchpad — September 19, 2011 @ 4:58 pm BST Sep 19,2011 | Reply

  10. Did Sokrates mean this?

    http://en.wikipedia.org/wiki/Tautology_%28logic%29#Efficient_verification_and_the_Boolean_satisfiability_problem

    Kind regards


    Kamal

    Comment by Kamal — September 19, 2011 @ 6:07 pm BST Sep 19,2011 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,257 other followers