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 DROP a CHECK CONSTRAINT in SQL / PLSQL is:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
Example:
ALTER TABLE employee DROP CONSTRAINT employee_pk;
The above statement will drop the check constraint ‘employee_pk’ from the ‘employee’ table.
Disable a CHECK CONSTRAINT.
If we do not wish to delete the check constraint as we may need the same in future but for some time we want the check constraint not to function, then we can disable the check constraint.
Syntax for disabling the check constraint is:
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;
Example:
ALTER TABLE employee DISABLE CONSTRAINT employee_pk;
Here in the above ALTER statement we have disabled the CHECK CONSTRAINT ‘employee_pk’ on the ‘employee’ table.
Enabling a CHECK CONSTRAINT
We can enable a check constraint that has been disabled earlier, the syntax for enabling a check constraint is:
ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;
Example:
ALTER TABLE employee ENABLE CONSTRAINT employee_pk;
Here in the above statement we have enabled the check constraint ‘employee_pk’ in ‘employee’ table using ALTER TABLE statement.