Friday 16 August 2024

On Database Constraints in Oracle

So there was something confusing about a check constraint in one of my tables.

I've proven that the check constraint works as it should, but I failed to understand why.

Actually there were two constraints that sort of combine.

Let's say I have a table called "customers". And that there are two constraints at play:

constraint check01 check (bankaccountnr is not null or creditcardnr is not null or paypalemail is not null)
constraint check02 check (bankaccountnr > 0 and creditcardnr > 0 and length(paypalemail) > 0)

The first constraint is obvious, at least one of the fields must be filled in, in order to process orders.

It's the second constraint that is confusing.

I thought that, because of the and, the check02 constraint should fire if I try to enter only a bankaccountnr (as the other two conditions evaluate to false).

And I thought I verified that by using the query:

select * from customers where bankaccountnr > 0 and creditcardnr > 0 and length(paypalemail) > 0;

As I suspected it provided me ONLY the records that had ALL fields entered properly.

But apparently, in (check) constraints, if the condition is "unknown", for example in the case of NULL-values, it is accepted.

According to the following quote, found in [1] which I found via [2]:

A check constraint on a column or set of columns requires that a specified condition be true or unknown for every row. If DML results in the condition of the constraint evaluating to false, then the SQL statement is rolled back.