CHECK Constraint in Oracle

CHECK constraint defines a rule or condition which every row must satisfy
A single column can have multiple CHECK constraint
There is no limit to define no of CHECK constraint on a column
CHECK constraint can be added both at column level or table level

Types of CHECK KEY Constraints:

CHECK constraint can be defined on a single column only. It cannot be composite.

COLUMN LEVEL DEFINITION:

CREATE TABLE EMP_CHECK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_CHK_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2) CONSTRAINT EMP_CHECK_SAL CHECK(SALARY > 99),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4)
);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_CHECK_TAB';

CHECK Constraint in Oracle : output

SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME,
       POSITION
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_CHECK_TAB';

TABLE LEVEL DEFINITION:

SINGLE CHECK CONSTRAINT ON A SINGLE COLUMN:

DROP TABLE EMP_CHECK_TAB;

CREATE TABLE EMP_CHECK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_CHK_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4),
 CONSTRAINT EMP_CHECK_SAL CHECK(SALARY > 99)
);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_CHECK_TAB';

CHECK Constraint in Oracle : output

MULTIPLE CHECK CONSTRAINT ON A SINGLE COLUMN:

CREATE TABLE EMP_CHECK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_CHK_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4),
 CONSTRAINT EMP_CHECK_SAL CHECK(SALARY > 99),
 CONSTRAINT EMP_CHECK_SAL_NULL CHECK(SALARY IS NOT NULL)
);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_CHECK_TAB';

SELECT OWNER, 
       CONSTRAINT_NAME, 
       TABLE_NAME, 
       COLUMN_NAME,
       POSITION
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'EMP_CHECK_TAB';

Types of CHECK constraint definition:

In-line definition
Out-of-line definition

In-line definition:

It states that CHECK constraint is defined during table creation, either at column level or at table level.

COLUMN LEVEL DEFINITION:

DROP TABLE EMP_CHECK_TAB;

CREATE TABLE EMP_CHECK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_CHK_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2) CONSTRAINT EMP_CHECK_SAL CHECK(SALARY > 99),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4)
);

TABLE LEVEL DEFINITION:

DROP TABLE EMP_CHECK_TAB;

CREATE TABLE EMP_CHECK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_CHK_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4),
 CONSTRAINT EMP_CHECK_SAL CHECK(SALARY > 99),
 CONSTRAINT EMP_CHECK_SAL_NULL CHECK(SALARY IS NOT NULL)
);

Out-of-line definition:

It states that CHECK constraint is defined outside of CREATE TABLE statement.

DROP TABLE EMP_CHECK_TAB;

CREATE TABLE EMP_CHECK_TAB
(EMPLOYEE_ID     NUMBER(6) CONSTRAINT EMP_CHK_PK PRIMARY KEY,
 FIRST_NAME      VARCHAR2(30),
 LAST_NAME       VARCHAR2(30),
 JOB_ID          VARCHAR2(10),
 SALARY          NUMBER(8, 2),
 MANAGER_ID      NUMBER(6),
 DEPARTMENT_ID   NUMBER(4)
);

USING MODIFY STATEMENT:

ALTER TABLE EMP_CHECK_TAB MODIFY (SALARY NUMBER(8, 2) CONSTRAINT EMP_CHECK_SAL CHECK(SALARY > 99));

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_CHECK_TAB';

CHECK Constraint in Oracle : output

USING ADD CONSTRAINT STATEMENT:

ALTER TABLE EMP_CHECK_TAB DROP CONSTRAINT EMP_CHECK_SAL;

ALTER TABLE EMP_CHECK_TAB ADD CONSTRAINT EMP_CHECK_SAL CHECK(SALARY > 99);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_CHECK_TAB';

CHECK Constraint in Oracle : output

Disabling CHECK Constraint:

ALTER TABLE EMP_CHECK_TAB DISABLE CONSTRAINT EMP_CHECK_SAL;

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_CHECK_TAB';

Enabling CHECK Constraint:

ALTER TABLE EMP_CHECK_TAB ENABLE CONSTRAINT EMP_CHECK_SAL;

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_CHECK_TAB';

CHECK Constraint in Oracle : output

Dropping CHECK Constraint:

ALTER TABLE EMP_CHECK_TAB DROP CONSTRAINT EMP_CHECK_SAL;

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_CHECK_TAB';

Impact of CHECK Constraint on DML operations:

SINGLE CHECK CONSTRAINT ON A SINGLE COLUMN:

INSERT:

ALTER TABLE EMP_CHECK_TAB ADD CONSTRAINT EMP_CHECK_SAL CHECK(SALARY > 99);

Inserting one row into EMP_CHECK_SAL table with salary less than 99:

INSERT INTO EMP_CHECK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (201,'Michael','Hartstein','MK_MAN',98,100,20);

CHECK Constraint in Oracle : error output

So oracle is not allowing to insert salary is less than or equals to 99.

Inserting row with salary > 99:

INSERT INTO EMP_CHECK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (201,'Michael','Hartstein','MK_MAN',100,100,20);

SELECT * FROM EMP_CHECK_TAB;

UPDATE:

Trying to update salary with value is less than or equals to 99:

UPDATE EMP_CHECK_TAB
SET SALARY = 98
WHERE EMPLOYEE_ID = 201;

Updating salary with value greater than 99:

UPDATE EMP_CHECK_TAB
SET SALARY = 102
WHERE EMPLOYEE_ID = 201;

Inserting row with salary as NULL:

INSERT INTO EMP_CHECK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (202,'Pat','Fay','MK_REP',NULL,201,20);

So CHECK constraint does not restrict NULL values. Because NULL is not comparable to any value.

MULTIPLE CHECK CONSTRAINTS ON A SINGLE COLUMN:

DELETE EMP_CHECK_TAB;

ALTER TABLE EMP_CHECK_TAB DROP CONSTRAINT EMP_CHECK_SAL;

ALTER TABLE EMP_CHECK_TAB ADD CONSTRAINT EMP_CHECK_SAL CHECK(SALARY IS NOT NULL);

ALTER TABLE EMP_CHECK_TAB ADD CONSTRAINT EMP_CHECK_SAL CHECK(SALARY > 99);

Querying the data dictionary:

SELECT OWNER, 
       CONSTRAINT_NAME, 
       CONSTRAINT_TYPE, 
       TABLE_NAME, 
       SEARCH_CONDITION, 
       STATUS, 
       DEFERRABLE, 
       DEFERRED, 
       VALIDATED 
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMP_CHECK_TAB';

INSERT:

Inserting row with salary as NULL:

INSERT INTO EMP_CHECK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (202,'Pat','Fay','MK_REP',NULL,201,20);

Inserting one row into EMP_CHECK_SAL table with salary is less than or equals to 99:

INSERT INTO EMP_CHECK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (202,'Pat','Fay','MK_REP',98,201,20);

So we can see that both the CHECK constraints are working fine.

Inserting row with salary > 99:

INSERT INTO EMP_CHECK_TAB (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_ID,SALARY,MANAGER_ID,DEPARTMENT_ID) 
VALUES (202,'Pat','Fay','MK_REP',102,201,20);

SELECT * FROM EMP_CHECK_TAB;

UPDATE:

Updating row with salary as NULL:

UPDATE EMP_CHECK_TAB
SET SALARY = NULL
WHERE EMPLOYEE_ID = 202;

Updating row with salary is less than or equals to 99:

UPDATE EMP_CHECK_TAB
SET SALARY = 99
WHERE EMPLOYEE_ID = 202;

So both the CHECK constraints are working fine.

Updating row with salary greater than 99:

UPDATE EMP_CHECK_TAB
SET SALARY = 105
WHERE EMPLOYEE_ID = 202;

SELECT * FROM EMP_CHECK_TAB;

RELATED TOPICS:

sayan_bappa@yahoo.co.in

View all posts

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

CHECK Constraint in Oracle