Check constraint evaluation

Perhaps one of the most confusing aspects in dealing with relational databases is dealing with the concept of null. In particular, logical expressions involving null can evaluate to one of three possible outcomes, not just to the traditional “true” and “false” but also “unknown”.

When we look at a typical WHERE clause in a SELECT query, a logical expression is deemed to have been successfully passed if the expression outcome is “true”. Outcomes of “false” and “unknown” are rejected. To provide an example, if we want to identify employees with a salary of $2,000 or greater we would write our query as:

SELECT ename
FROM   emp
WHERE  sal >= 2000

As well as not selecting those with a salary less than $2,000, anyone without a salary is excluded from the above query on the basis that the WHERE clause expression has a logical result of “unknown”.

In contrast to the way queries operate, check constraint expressions are passed if they do not evaluate to “false”. Hence a check constraint is successfully passed if the logical expression evaluates to either “true” or “unknown”. Taking our previous example, suppose we want to add a constraint onto our employees table to prevent negative salaries, i.e. we think that that people who pay for the pleasure of turning up to work are crazy and have no place in our organisation, then we might add the following:

   ADD CONSTRAINT emp_chk1
   CHECK (sal >= 0)

With this check constraint in place an employee row with a null salary passes the check constraint and is therefore permitted, whilst a negative salary is prohibited:

  2     (empno
  3     ,ename
  4     ,job
  5     ,sal)
  7    (1234
  8    ,'MARK'
  9    ,'DEVELOPER'
 10    ,NULL -- note the NULL salary
 11    );

1 row created.

  2      (empno
  3      ,ename
  4      ,job
  5      ,sal)
  7     (2345
  8     ,'CARL'
  9     ,'DEVELOPER'
 10     ,-2000 -- note the negative salary
 11     );
ERROR at line 1:
ORA-02290: check constraint (DEVELOPER.EMP_CHK1) violated

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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 )

Connecting to %s