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
SQL> create index hr.EMP_ID_ST_DT_PK on hr.job_history (employee_id, start_date);
SQL> select uniqueness from user_indexes where index_name = 'EMP_ID_ST_DT_PK';
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;
We did it.
If you had some other use cases that need non-unique primary keys, please leave your comment to let me know.