Part 4: Check Constraints in SQL

A check constraint on a column or set of columns requires that a specified condition be true or unknown for every row.

A check constraint lets you specify a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). When Oracle evaluates a check constraint condition for a particular row, any column names in the condition refer to the column values in that row.

If DML results in the condition of the constraint evaluating to false, then the SQL statement is rolled back. The chief benefit of check constraints is the ability to enforce very specific integrity rules. For example, you could use check constraints to enforce the following rules in the hr.employees table:

• The salary column must not have a value greater than 10000.

• The commission column must have a value that is not greater than the salary.

The following example creates a maximum salary constraint on employees and demonstrates what happens when a statement attempts to insert a row containing a salary that exceeds the maximum:

SQL> ALTER TABLE employees ADD CONSTRAINT max_emp_sal CHECK (salary <
10001);
SQL> INSERT INTO employees
(employee_id,last_name,email,hire_date,job_id,salary)
 1 VALUES (999,'Green','BGREEN',SYSDATE,'ST_CLERK',20000);
.
.
.
ERROR at line 1:
ORA-02290: check constraint (HR.MAX_EMP_SAL) violated

A single column can have multiple check constraints that reference the column in its definition. For example, the salary column could have one constraint that prevents values over 10000 and a separate constraint that prevents values less than 500.

Restrictions on Check Constraints

Check constraints are subject to the following restrictions:

• You cannot specify a check constraint for a view. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.

• The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.

• Conditions of check constraints cannot contain the following constructs:

– Subqueries and scalar subquery expressions

– Calls to the functions that are not deterministic (CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP,SESSIONTIMEZONE, SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)

– Calls to user-defined functions

– Dereferencing of REF columns (for example, using the DEREF function)

– Nested table columns or attributes

– The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM

– Date constants that are not fully specified

– You cannot specify a check constraint for an external table.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar2(255) NOT NULL,
    FirstName varchar2(255),
    Age number,
    City varchar2(255),
    CONSTRAINT CHK_Person CHECK (Age>=21 AND City='PUNE')
);

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=21 AND City='PUNE');

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;