DB Version : 10gR2
I have a scenario in which i need to keep some duplicate values in a table. But, i want to create a Unique key on this column but the existing duplicate records should be ignored. I tried ENABLE NOVALIDATE. But it doesn't work. Is there a workaround for this without deleting the duplicate records?
create table roller (col1 number);
insert into roller values (1);
insert into roller values (1);
insert into roller values (2);
insert into roller values (3);
alter table roller add constraint uniq_roller unique (col1) ENABLE NOVALIDATE;
ERROR at line 1:
ORA-02299: cannot validate (IT_TOGO.UNIQ_ROLLER) - duplicate keys found