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