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:

ALTER TABLE emp
   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:

SQL>INSERT INTO emp
  2     (empno
  3     ,ename
  4     ,job
  5     ,sal)
  6  VALUES
  7    (1234
  8    ,'MARK'
  9    ,'DEVELOPER'
 10    ,NULL -- note the NULL salary
 11    );

1 row created.

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

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s