Oracle/SQL DROP, DISABLE, ENABLE PRIMARY Key

Oracle SQL allows to DROP PRIMARY KEY, DISABLE and ENABLE a PRIMARY KEY Constraint using the SQL ALTER TABLE Statement.

In other words we can say that, whenever we want to Drop, Disable or Enable a PRIMARY KEY in SQL, we have to use the ALTER TABLE Statement.

Important points about PRIMARY KEYS in Oracle PL/SQL:

1. A table can have one and only one primary key.
2. In Oracle SQL / PLSQL a primary key can be created by using maximum of 32 columns / fields.
3. The columns / fields that are used to define a primary key on table cannot have NULL values.
4. A primary key can be dropped, disabled and enabled in ALTER TABLE statement.

DROP PRIMARY KEY Constraint Syntax

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

DROP PRIMARY KEY Constraint Example

The below example will DROP the PRIMARY KEY ‘employee_pk’ from ‘employee’ table.

ALTER TABLE employee
DROP CONSTRAINT employee_pk;

DISABLE PRIMARY KEY Constraint Syntax

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

DISABLE PRIMARY KEY Constraint Example

The below example will DISABLE the PRIMARY KEY ‘employee_pk’ from ‘employee’ table.

ALTER TABLE employee
DISABLE CONSTRAINT employee_pk;

To DISABLE a PRIMARY key can be a handy option if we wish to use the same PRIMARY key in near future.

ENABLE PRIMARY KEY Constraint Syntax

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

ENABLE PRIMARY KEY Constraint Example

The below example will ENABLE the PRIMARY KEY ‘employee_pk’ of ‘employee’ table

ALTER TABLE employee
ENABLE CONSTRAINT employee_pk;

We can ENABLE only those PRIMARY KEYS which have been DISABLED earlier