«Back to Home

Oracle Jump Start
Topics

How To Use Check Constraints in OraclePLSQL

Description
 
A check constraints allows you to specify a condition on each row in a table.
  • In Oracle, check constraints have some limitations; subqueries cannot be used within Oracle check constraints. 
  • Check constraints cannot reference the columns from the other tables.
  • Oracle check constraints needs trigger, which must be implemented.
  • Oracle check constraint validates incoming columns at row insert time.
  • Check constraints cannot contain subqueries and scalar sub queries expression.
Syntax
  1. Create table table_name  
  2. (  
  3. column 1 data type (),  
  4. column 2 data type (),  
  5. .  
  6. .  
  7. CONSTRAINT constraint_name check (column_name condition)  
  8. );   
Example
  1. CREATE TABLE suppliers  
  2. (  
  3. supplier_id numeric(20),  
  4. supplier_name varchar2(50),  
  5. supplier_contact numeric(10),  
  6. supplier_address varchar2(255),  
  7. CONSTRAINT check_supplier_id  
  8. CHECK (supplier_id BETWEEN 100 and 200)  
  9. );  
1

Enabling Check Constraints
 
Syntax
  1. Alter table table_name  
  2. Enable Constraint Constraint_name;  
Example
 
In the example, given below, we are enabling check constraints from the suppliers table called
check_supplier_id.
  1. Alter table suppliers  
  2. Enable CONSTRAINT check_supplier_id;  
2 
 
Disable Check Constraint
 
Syntax
  1. Alter table table_name  
  2. Disable Constraint Constraint_name;  
Example
 
In the example, given below, we are disabling a check constraint from the suppliers table called
check_supplier_id.
  1. Alter table suppliers  
  2. Disable CONSTRAINT check_supplier_id;  
3
Drop a Check Constraint
 
Syntax
  1. Alter table table_name  
  2. Drop Constraint constraint_name;  

Example
 
In the example, given below, we are dropping a check constraint from the suppliers table called
check_supplier_id.
  1. Alter table Suppliers  
  2. Drop Constraint check_supplier_id;   
4
 
Summary
Thus, we learnt, a check constraint allows you to specify a condition on each row in a table. We learnt, how to use check constraints in Oracle with the examples.