How to Create a Non-unique Primary Key

  • by

The primary key in a table is just like the spine of a human being, which integrates all data in the table. In practice, we usually create unique primary keys for tables to keep data clean and consistent.

Can we create non-unique primary key for tables? Yes, we can. In some special situations, we create non-unique primary keys for developing and testing purposes.

In this post, we take the following steps to create a non-unique primary key for a table.

1. Drop Current Primary Key

First of all, you have to drop the current primary key from the table if there's any.

2. Create a Non-unique Index

Next, we create a non-unique index like this:

SQL> conn hr/hr
Connected.
SQL> create index hr.EMP_ID_ST_DT_PK on hr.job_history (employee_id, start_date);

Index created.

SQL> select uniqueness from user_indexes where index_name = 'EMP_ID_ST_DT_PK';

UNIQUENES
---------
NONUNIQUE

Without UNIQUE modifier in the statement, this index is created as a non-unique one. By the way, there're more CREATE INDEX syntax for specific release.

3. Add Primary Key

Then we 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.

We did it.

If you had some other use cases that need non-unique primary keys, please leave your comment to let me know.

Leave a Reply

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