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
Mark Hoxey 