Create a Unique Constraint Ignoring Existing Duplicate Data

Hi,

There was an interesting post on OTN forums, where the OP asks:

Can we create a unique index on a column having duplicate values which can enforce the uniqueness for future values.?

Well, this is possible as Oracle is rich in features. It can be achieved by creating a unique constraint with DEFERRABLE and NOVALIDATE keywords. By default, constraints are created as NON DEFERRABLE and VALIDATE. When we create a constraint with DEFERRABLE and NOVALIDATE, we instruct Oracle to defer checking existing data for uniqueness but always check the new or modified rows.

Here's how we can do this:

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t
  2  ( x int );

Table created.

SQL>
SQL>
SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 1);

1 row created.

SQL>
SQL> alter table t add constraint t_uk unique (x)
  2    deferrable initially immediate novalidate;

Table altered.

SQL> select * from t;

         X
----------
         1
         1

SQL>
SQL> alter table t modify constraint t_uk enable novalidate;

Table altered.

SQL>
SQL> insert into t values ( 1);
insert into t values ( 1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T_UK) violated


SQL> select * from t;

         X
----------
         1
         1

SQL> insert into t values (2);

1 row created.

SQL>

Happy reading !!!