• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / sql / CHECK CONSTRAINT in Oracle SQL – PLSQL

CHECK CONSTRAINT in Oracle SQL – PLSQL

October 31, 2012 by techhoneyadmin

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:

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

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.

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

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

Now, if we query the employee table as:

SELECT *
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:

INSERT INTO employee
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:

ALTER TABLE employee
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.


Filed Under: sql Tagged With: check constraint in oracle plsql, check constraint in oracle sql, CheckConstraintsPLSQL, how to use check constraint in oracle database query, how to use check constraint in oracle plsql, how to use check constraint in oracle sql, syntax and example of check constraint in oracle database query, syntax and example of check constraint in oracle plsql, syntax and example of check constraint in oracle sql, using check constraint in oracle database query, using check constraint in oracle plsql, using check constraint in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in