• Post author:
  • Post category:All / SQL
  • Reading time:2 mins read
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:

Advertisement

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

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

Advertisement

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:

Advertisement

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

Select * from Empdup;

Related:  Writing Text Files On The Client in Oracle Forms 10g

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.