How to Modify Primary Key

  • by

Modify Primary Key

Sometimes, developers may design a better primary key to support the uniqueness of a table and they want to replace the current one with the new one, but the problem is that there's no syntax to support modifying the composition of a primary key directly, so we should take it as two steps, which are:

  1. DROP the primary key
  2. ADD the primary key

The only syntax supported by Oracle database is to modify the state of the primary key. For example, to disable the primary key constraint, we can do this:

SQL> alter table hr.job_history modify primary key disable;

Table altered.

Additionally, to disable the primary key constraint as well as the foreign key, we can add CASCADE to it:

SQL> alter table hr.job_history modify primary key disable cascade;

Table altered.

Leave a Reply

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