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'; 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'; 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'; 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'; 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'; 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);
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: