Check constraint

From Oracle FAQ
Jump to: navigation, search

A check constraint is a user-defined condition that must evaluate to TRUE or NULL for a column value to be valid. For example, you can define a constraint to ensure no employee is paid less than the minimum wage, say (salary > 1000). If the condition is violated (after an Insert or Update), the entire transaction will be rolled back.

[edit] Examples

Define check contraint in-line:

CREATE TABLE emp (empno NUMBER PRIMARY KEY,
                  ename VARCHAR2(20),
                  sal   NUMBER(10,2) CHECK (sal between 1000 and 20000)
);

Define check contraint out-of-line:

CREATE TABLE emp (empno NUMBER PRIMARY KEY,
                  ename VARCHAR2(20),
                  sal   NUMBER(10,2),
                  CONSTRAINT salcheck
                       CHECK (sal between 1000 and 20000)
);

Try to insert invalid data:

INSERT INTO emp VALUES (1, 'Frank', 10)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SALCHECK) violated

[edit] Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #