Add Unique key in a table that contains duplicate row

Requirement : A table contains some duplicate data. Now we want to add a unique constraint that skip existing duplicate values but check newly inserted duplicate values.




SQL> create table t2 (id number(10), t varchar2(20));



Table created.





SQL> insert into t2 values (1,'A');



1 row created.



SQL> insert into t2 values (1,'A');



1 row created.



SQL> insert into t2 values (1,'A');



1 row created.



SQL> commit;



Commit complete.



SQL> alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE;

alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE

*

ERROR at line 1:

ORA-02299: cannot validate (HASAN.UK_T2) - duplicate keys found





SQL> select * from t2;



ID T

---------- ------------------------------------------------------------

1 A

1 A

1 A



So normal method does not work !







Case 1: New Table That means initially the table does not have any data



SQL> create table t3 (id number(10), t varchar2(20));



Table created.



SQL> alter table t3 add constraint gpu unique (id) deferrable initially deferred;



Table altered.



SQL> alter table t3 disable constraint gpu;



Table altered.





SQL> insert into t3 values(1,'A');



1 row created.



SQL> insert into t3 values(1,'A');



1 row created.



SQL> insert into t3 values(1,'A');



1 row created.



SQL> commit;



Commit complete.



SQL>

SQL>

SQL>

SQL> select * from t3;



ID T

---------- ------------------------------------------------------------

1 A

1 A

1 A







SQL> alter table t3 enable novalidate constraint gpu;



Table altered.



SQL> insert into t3 values(2,'A');



1 row created.



SQL> commit;



Commit complete.



SQL> insert into t3 values(2,'A');



1 row created.



SQL> commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-00001: unique constraint (HASAN.GPU) violated



SQL> alter table t3 modify constraint gpu INITIALLY IMMEDIATE;



Table altered.



SQL> insert into t3 values(1,'A');

insert into t3 values(1,'A')

*

ERROR at line 1:

ORA-00001: unique constraint (HASAN.GPU) violated







Case 2: Existing Table that contains duplicate data





SQL> create table t2 (id number(1),a varchar2(10));



Table created.





SQL> insert into t2 values(1,'A');



1 row created.



SQL> insert into t2 values(1,'A');



1 row created.



SQL> commit;



Commit complete.



SQL> alter table t2 add constraint uk_t2 unique(id) DEFERRABLE INITIALLY DEFERRED disable;



Table altered.



SQL> alter table t2 enable novalidate constraint uk_t2;



Table altered.



SQL> insert into t2 values(1,'A');



1 row created.



SQL> commit;

commit

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-00001: unique constraint (HASAN.UK_T2) violated





SQL> alter table t2 modify constraint uk_t2 INITIALLY IMMEDIATE;



Table altered.



SQL> insert into t2 values(1,'A');

insert into t2 values(1,'A')

*

ERROR at line 1:

ORA-00001: unique constraint (HASAN.UK_T2) violated





OR





SQL> alter table t2 add constraint uk_t2 unique(id) disable;



Table altered.





SQL> alter table t2 enable novalidate constraint uk_t2;



Table altered.



SQL> insert into t2 values(1,'A');

insert into t2 values(1,'A')

*

ERROR at line 1:

ORA-00001: unique constraint (HASAN.UK_T2) violated