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