In Oracle SQL / PLSQL a CHECK CONSTRAINT allows us to specify a condition on each record / row of a table.
Important points about CHECK CONSTRAINT in Oracle SQL / PLSQL:
- In Oracle SQL / PLSQL a CHECK CONSTRAINT can never be defined on a VIEW but only on a table.
- A CHECK CONSTRAINT on a table can refer to columns of the same table and not of any other table.
- No sub-query is allowed in the CHECK CONSTRAINT.
- A CHECK CONSTRAINT can be created using CREATE TABLE or ALTER TABLE statement.
Syntax to create a CHECK CONSTRAINT in CREATE TABLE statement in Oracle SQL / PLSQL is:
CREATE TABLE table_name
(column_name1 datatype NULL/NOT NULL
,column_name2 datatype NULL/NOT NULL
,column_name3 datatype NULL/NOT NULL
.
.
,column_nameN datatype NULL/NOT NULL
,CONSTRAINT constraint_name CHECK (column_name condition ) [DISABLE];
Note: The DISABLE keyword is optional, if we create a CHECK CONSTRAINT with disable keyword then the constraint will be created but will not be enforced.
Syntax to create a CHECK CONSTRAINT in ALTER TABLE statement is :
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];
Let’s take an example for understanding:
Scenario 1:
Step 1: Suppose we want to create a table named ‘employee’ in the database as shown below, with ‘employee_id’ column having CHECK CONSTRAINT.
Employee_Id | Employee_Name | Salary | Department | Commission |
101 | Emp A | 10000 | Sales | 10 |
102 | Emp B | 20000 | IT | 20 |
103 | Emp C | 28000 | IT | 20 |
104 | Emp D | 30000 | Support | |
105 | Emp E | 32000 | Sales | 10 |
We can achieve the same as:
1 | CREATE TABLE employee |
2 | (employee_id NUMBER(10) NOT NULL |
3 | ,employee_name VARCHAR2(500) NOT NULL |
4 | ,salary NUMBER(20) NOT NULL |
5 | ,department VARCHAR2(300) NOT NULL |
6 | ,commission NUMBER(20) |
7 | , CONSTRAINT employee_pk CHECK (employee_id BETWEEN 101 AND 500) |
8 | ); |
Here with the help of the above SQL CREATE statement we have created a table named ‘employee’ that has 5 columns namely ‘employee_id’, ‘employee_name’, ‘department’, ‘salary’ and ‘commission’.
Also we are having CHECK CONSTRAINT on ‘employee_id’ column which ensures that we do not enter any employee_id less than 101 or more than 500.
Step 2: Inserting the data in the ‘employee’ table.
1 | INSERT INTO employee |
2 | VALUES (101, 'Emp A' ,10000, 'Sales' ,10); |
3 |
4 | INSERT INTO employee |
5 | VALUES (102, 'Emp B' ,20000, 'IT' ,20); |
6 |
7 | INSERT INTO employee |
8 | VALUES (103, 'Emp C' ,28000, 'IT' ,20); |
9 |
10 | INSERT INTO employee |
11 | VALUES (104, 'Emp D' ,30000, 'Support' , NULL ); |
12 |
13 | INSERT INTO employee |
14 | VALUES (105, 'Emp E' ,32000, 'Sales' ,10); |
The above SQL INSERT statements will insert 5 rows in the ‘employee’ table.
Now, if we query the employee table as:
1 | SELECT * |
2 | FROM employee; |
We will get the following result:
Employee_Id | Employee_Name | Salary | Department | Commission |
101 | Emp A | 10000 | Sales | 10 |
102 | Emp B | 20000 | IT | 20 |
103 | Emp C | 28000 | IT | 20 |
104 | Emp D | 30000 | Support | |
105 | Emp E | 32000 | Sales | 10 |
Here we have successfully created ‘employee’ table with column ‘employee_id’ having UNIQUE CONSTRAINT and also inserted data in ‘employee’ table.
If we try to insert one more record of employee_id = 50 as:
1 | INSERT INTO employee |
2 | VALUES (50, 'Emp F' ,40000, 'Sales' ,30); |
We get an error that check constraint is getting violated and the data cannot be entered, this is because ‘employee_id’ column has CHECK CONSTRAINT that enforces a condition that the ‘employee_id’ entered must be greater than 100 and lesser than 500 .
Scenario 2:
Using ALTER TABLE statement to create CHECK CONSTRAINT on a table
As mentioned earlier that an ALTER TABLE statement can be used to create CHECK CONSTRAINT on a table.
The syntax to create CHECK CONSTRAINT using ALTER TABLE statement is:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];
Example:
1 | ALTER TABLE employee |
2 | ADD CONSTRAINT employee_pk CHECK (employee_id BETWEEN 101 AND 500); |
Here we have altered the ‘employee’ table and have successfully created CHECK CONSTRAINT on ‘employee_id’ column.