Skip to content

How to Drop Primary Key in Oracle

Drop Primary Key

Primary key is sort of a constraint rather than an index, you should treat it as a constraint first so as to drop it successfully.

That is to say, you have to drop the primary key constraint first. If necessary, drop the unique index subsequently.

1. Drop Primary Key Constraint

When You Know the Constraint Name

SQL> alter table hr.job_history drop constraint JHIST_EMP_ID_ST_DATE_PK;

Table altered.

The above syntax can be applied to any type of constraint. In other words, you don't have to add Oracle SQL keywords PRIMARY KEY in this statement.

When You Don't Know the Constraint Name

If you don't know the constraint name, you can do this:

SQL> alter table hr.job_history drop primary key;

Table altered.

Instead, you have to add the Oracle SQL keywords PRIMARY KEY in this statement.

Normally, the above statements can remove the primary key completely without any footprint. But in some cases, they leave an unique index to be removed. This is because the primary key was based on an existing unique index by USING INDEX clause.

If you decide to remove the index, we should move on.

2. Drop Unique Index

You have to know the name of the primary key in order to drop it.

SQL> drop index hr.JHIST_EMP_ID_ST_DATE_PK;

Index dropped.

Further reading: Oracle DROP INDEX Syntax for All Releases.

In summary, the key step is to drop the primary key constraint, not the index. If you drop the index in the first place, you'll get ORA-02429.

To add a primary key back to a table, you may like to know how to add the primary key to a table.

Leave a Reply

Your email address will not be published. Required fields are marked *