In Oracle SQL / PLSQL a UNIQUE CONSTRAINT is single or group of column(s)/field(s) that can uniquely identify each record in a table.
Some of the column(s) / field(s) of unique constraint can have NULL values until the combination can uniquely identify each record of table.
Important points about UNIQUE CONSTRAINT in Oracle SQL / PLSQL:
- In Oracle SQL / PLSQL a UNIQUE CONSTRAINT can be created by using maximum of 32 columns / fields.
- A UNIQUE CONSTRAINT can be dropped, disabled and enabled in ALTER TABLE statement.
Syntax to DROP a UNIQUE 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 unique constraint ‘employee_pk’ from the ‘employee’ table.
Disable a UNIQUE CONSTRAINT.
If we do not wish to delete the unique constraint as we may need the same in future but for some time we want the unique constraint not to function, then we can disable the unique constraint
Syntax for disabling the unique constraint in Oracle SQL / PLSQL 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 UNIQUE CONSTRAINT ‘employee_pk’ on the ‘employee’ table.
Enabling a UNIQUE CONSTRAINT
We can enable a unique constraint that has been disabled earlier, the syntax for enabling a unique constraint in Oracle SQL / PLSQL 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 unique constraint ‘employee_pk’ in ‘employee’ table using ALTER TABLE statement.