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