CHECK CONSTRAINT in Oracle SQL – PLSQL

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:

  1. In Oracle SQL / PLSQL a CHECK CONSTRAINT can never be defined on a VIEW but only on a table.
  2. A CHECK CONSTRAINT on a table can refer to columns of the same table and not of any other table.
  3. No sub-query is allowed in the CHECK CONSTRAINT.
  4. 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:

[sourcecode language=”sql”]
CREATE TABLE employee
(employee_id NUMBER(10) NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary NUMBER(20) NOT NULL
,department VARCHAR2(300) NOT NULL
,commission NUMBER(20)
,CONSTRAINT employee_pk CHECK (employee_id BETWEEN 101 AND 500)
);
[/sourcecode]

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.

[sourcecode language=”sql”]
INSERT INTO employee
VALUES (101,’Emp A’,10000,’Sales’,10);

INSERT INTO employee
VALUES (102,’Emp B’,20000,’IT’,20);

INSERT INTO employee
VALUES (103,’Emp C’,28000,’IT’,20);

INSERT INTO employee
VALUES (104,’Emp D’,30000,’Support’,NULL);

INSERT INTO employee
VALUES (105,’Emp E’,32000,’Sales’,10);
[/sourcecode]

The above SQL INSERT statements will insert 5 rows in the ‘employee’ table.

Now, if we query the employee table as:

[sourcecode language=”sql”]
SELECT *
FROM employee;[/sourcecode]

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:

[sourcecode language=”sql”]
INSERT INTO employee
VALUES (50,’Emp F’,40000,’Sales’,30);
[/sourcecode]

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:

[sourcecode language=”sql”]
ALTER TABLE employee
ADD CONSTRAINT employee_pk CHECK(employee_id BETWEEN 101 AND 500);
[/sourcecode]

Here we have altered the ‘employee’ table and have successfully created CHECK CONSTRAINT on ‘employee_id’ column.


Leave a Reply

Your email address will not be published. Required fields are marked *