How To Use Check Constraints in OraclePLSQL
Description
- 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
- Create table table_name
- (
- column 1 data type (),
- column 2 data type (),
- .
- .
- CONSTRAINT constraint_name check (column_name condition)
- );
Example
- CREATE TABLE suppliers
- (
- supplier_id numeric(20),
- supplier_name varchar2(50),
- supplier_contact numeric(10),
- supplier_address varchar2(255),
- CONSTRAINT check_supplier_id
- CHECK (supplier_id BETWEEN 100 and 200)
- );

Enabling Check Constraints
Syntax
- Alter table table_name
- Enable Constraint Constraint_name;
Example
check_supplier_id.
- Alter table suppliers
- Enable CONSTRAINT check_supplier_id;

Disable Check Constraint
Syntax
- Alter table table_name
- Disable Constraint Constraint_name;
In the example, given below, we are disabling a check constraint from the suppliers table called
check_supplier_id.- Alter table suppliers
- Disable CONSTRAINT check_supplier_id;

Drop a Check Constraint
Syntax
- Alter table table_name
- Drop Constraint constraint_name;
Example
In the example, given below, we are dropping a check constraint from the suppliers table called
check_supplier_id.
- Alter table Suppliers
- Drop Constraint check_supplier_id;

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.