Tuple Rules! Using NVL2 and NULLIF.

6

While working on a current project I ran into the requirement that a column was mandatory, depending on the value of another column. The other way ’round was also true: if it had a certain value the other column must be NULL. The first column had a check constraint defined on it, so it could only have the values “I” and “O”, for “Incoming” and “Outgoing”.
When it contained the value of “I” the other column was mandatory. If it contained the value of “O” the other column must be NULL. So to recap valid combinations:

Valid I xxx
Valid O null
Not Valid I null
Not Valid O xxx


The table had multiple columns, but for simplicity sake, we’re using a table with just two columns.

create table t
(c1 varchar2(1)
,c2 varchar2(3)
)

The requirement stated that the first column (c1) has a value of “I”, as in “Incoming”, the second column (c2) is mandatory. When the first column has a value of “O”, as in “Outgoing” the second column must be NULL.
Could this requirement be implemented using a check-constraint?
Well,… yes! After some fiddling we came up with this solution:

alter table t add constraint t_chk
check (Decode (c2, null, 'O', 'I') = c1)

Not very exciting, but it does the job. The DECODE checks if the c2-column has a value in it, and returns either an “I” or an “O” and this is compared to the value of c1. This results in a True or False and either accepts or rejects the record.
Like I said before not really exciting.

Could a CASE-expression do the same? Of course!

alter table t add constraint t_chk
check (case when c2 is null then 'O' when c2 is not null then 'I' end = c1)

That looks nice. But also not really exciting, it just takes longer to type in the code. The biggest advantage over the DECODE is that the CASE expression is a lot easier to read and understand. It resemble a true “IF-THEN-ELSE” structure.

Is that it? What about some “new” functionality?
Well, how do you like this one:

alter table t add constraint t_chk
check (nullif (c1, decode (c2, null,'O', 'I')) is null)

NULLIF has been around since 8.1.7, but only if you use SQL*Loader. Since 9i it is also available in SQL. NULLIF can take two expressions which are compared. If they are equal, then the function returns null. If they are not equal, then the function returns the first expression.
The check compares the value of c1 against the “O” or “I” resulting from the DECODE in the second expression. If the first expression (c1) is equal to the DECODEd value of the second expression it would result in something similar like “NULL is NULL”.

Any other funky syntax I haven’t tried yet?

alter table t add constraint t_chk
check (nullif (c1, Nvl2 (c2, 'I', 'O')) is null)

The NVL2 was also introduced in 8.1.7 (release 3) . It takes three expressions. If the first expression is NOT NULL the function returns the second expression, otherwise the third expression.
For the check constraint, the NVL2 function would evaluate the c2-column, and return either an “I” or an “O” which is then compared to the value of column c1. Next is the NULLIF applied similar as before.

Four options, which one would I pick? Probably the DECODE or the CASE expression. Mainly because they are easy to read.
You can download a demo script here.

Share.

About Author

6 Comments

  1. Toon Koppelaars on

    OK, ‘unequals’ should be done with ‘<&gt’, right?
    alter table t add check(c1<>’I’ or c2 is not null);
    and
    alter table t add check(c1<>’O’ or c2 is null);

  2. Toon Koppelaars on

    Hmm. Some ‘==’ disappeared. It should have read:
    1) c1=’I’ ‘==>’ c2 is not null
    and
    2) c1=’O’ ‘==>’ c2 is null

  3. Toon Koppelaars on

    My 2 cents on this.

    I understand you actually have two tuple rules:
    1) c1=’I’ ==> c2 is not null
    and
    2) c1=’O’ ==> c2 is null
    The ‘==>’ above, is the logical implication (read as ‘implies’).

    To me this would then translate into two check constraints, enabling more detailed
    error messages. Using the rewrite rule for implication: “a implies b”, is logically
    equivalent to “not a or b”, this then results in:

    alter table t add check(c1<>‘I’ or c2 is not null);
    and
    alter table t add check(c1<>‘O’ or c2 is null);

    Nothing fancy here…

    T.

  4. Laurent Schneider on

    what do you think of
    c1 = ‘O’ and c2 is null or c1=’I’ and c2 is not null