How to Add Primary Key

  • by

Add Primary Key

There're 3 basic ways to create a primary key for a table.

1. System-Generated Name

You don't have to provide a name for the primary key, if you have no idea about the name of the primary key, you can let the database system generate one for you.

SQL> alter table hr.job_history add primary key (employee_id, start_date);

Table altered.

The system-generated name may look like this:

SYS_C0011315

To know the actual constraint name, you may refer to How to Check Primary Key of a Table.

2. User-Defined Name

You may also provide a name for the primary key constraint according to your naming convention.

SQL> alter table hr.job_history add constraint EMP_ID_ST_DT_PK primary key (employee_id, start_date);

Table altered.

Sometimes, I found user-defined names could distract developers from the correct way of troubleshooting.

3. Using an Existing Index

First of all, I created an unique index for later use.

SQL> create unique index hr.EMP_ID_ST_DT_PK on hr.job_history (employee_id, start_date);

Index created.

Then I use the index as a base for the primary key by USING INDEX clause while adding the constraint.

SQL> alter table hr.job_history add constraint EMP_ID_ST_DT_PK primary key (employee_id, start_date) using index hr.EMP_ID_ST_DT_PK;

Table altered.

Please note that, the name of the primary key constraint can be different from the unique index.

For your reference, there're more examples about how index associate with a constraint in the documentation.

To revert the action of adding primary key to a table, you may like to know how to drop the primary key from a table.

Leave a Reply

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