Creating Primary Key On A Field Containing Duplicate Values In Oracle

Yes, Oracle can have a primary key table containing duplicate values in particular field, but it doesn't mean that it will allow further duplicate values but it means that it will ignore the existing duplicate values while creating the primary key on that field.

Suppose you have a requirement that to keep existing table as is with the data but further you don't want to allow duplicate values in a particular field(s). 

So here is the solution, see the example below:

-- Create a table or use existing (which is containing duplicate values):

Create Table EmpDup (
ecode number(4),
ename varchar2(50),
sal number)
/
-- Create non unique index:

Create Index Indx_empdup on EmpDup(ecode);
-- Insert duplicate values:
Insert into EmpDup Values (1,'abc',5000);

Insert into EmpDup Values (1,'xyz', 9000);

Insert into EmpDup Values (2,'xyznew', 9000);

Commit;
-- Add primary key constraint, but with disable and keep index clause, it is necessary:

Alter Table EmpDup add constraint pk_ecode primary key(ecode)  disable keep index;
-- Now enable the constraint with novalidate clause to ignore the existing duplicate values:
Alter Table EmpDup modify constraint pk_ecode enable novalidate ;
-- Now try to insert duplicate values and it will not allow:

Insert into EmpDup Values (2,'xyznew2', 9000);
-- No Select the rows to check the data:

Select * from Empdup;

Share on Google Plus

About Mindsforest