DROPPING, DISABLING and ENABLING UNIQUE CONSTRAINT in Oracle SQL – PLSQL

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:

1. In Oracle SQL / PLSQL a UNIQUE CONSTRAINT can be created by using maximum of 32 columns / fields.
2. 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:

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:

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:

Here in the above statement we have enabled the unique constraint ‘employee_pk’ in ‘employee’ table using ALTER TABLE statement.