Tuple Rules! Using NVL2 and NULLIF.

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:

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.