Uniqueness using Non-Unique Index

How to add Primary Key index on table having duplicate data.

Many a times at our work location(s) it has been argued that only Unique Index can support Uniqueness in Table column.

Well! That’s not the case. A Non-Unique Index can also support Uniqueness in Column of Table.

I have demonstrated this in following statements.

 A table with Unique values.

SQL> Create table scott.test2 as select level id1 from dual connect by level<12;

  2

SQL> /

Table created.

Updated table to contain some duplicate values in ID1 Column.

SQL> Update scott.test2 set id1=3 where id1 in (1,2,4,5);

  2

SQL> /

4 rows updated.

SQL> commit;

Commit complete.

Now! Table Scott.TEST2 contains duplicate data in its ID1 Columns.

SQL> select * from scott.test2;

       ID1

----------

         3

         3              (Duplicate Data)

         3

         3

         3

         6

         7

         8

         9

        10

        11

11 rows selected.

Created a non-Unique(Default! If Create Statement is given by User) Index on SCOTT.TEST2.ID1 Column.

SQL> Create index t2ix on scott.test2(id1) compute statistics

  2  ;

Index created.

SQL>  select Index_name,Table_name,Table_owner,UNIQUENESS from dba_indexes where index_name='T2IX';

INDEX_NAME                     TABLE_NAME             TABLE_OWNER                    UNIQUENES

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

T2IX                                        TEST2                                   SCOTT                          NONUNIQUE

Trying to add Primary Key on SCOTT.TEST2.ID1 column but getting obvious error.

SQL> ALTER TABLE SCOTT.TEST2

  2  ADD CONSTRAINT T2_PK PRIMARY KEY(ID1);

ADD CONSTRAINT T2_PK PRIMARY KEY(ID1)

               *

ERROR at line 2:

ORA-02437: cannot validate (SCOTT.T2_PK) - primary key violated

Same error.

SQL> ALTER TABLE SCOTT.TEST2

  2  ADD CONSTRAINT T2_PK PRIMARY KEY(ID1) USING INDEX T2IX;

ADD CONSTRAINT T2_PK PRIMARY KEY(ID1) USING INDEX T2IX

               *

ERROR at line 2:

ORA-02437: cannot validate (SCOTT.T2_PK) - primary key violated

However! Trick lies in ENABLE and NOVALIDATE clauses.

Enable:- New Constraint is implemented. New Data will only be inserted if its Unique.

NoValidate:- Old data not to be  checked. Just ignore the old duplicate data.

SQL> ALTER TABLE SCOTT.TEST2

  2  ADD CONSTRAINT T2_PK PRIMARY KEY(ID1) USING INDEX T2IX ENABLE NOVALIDATE;

Table altered.

SQL> Insert into scott.test2 values(3)

  2  ;

Insert into scott.test2 values(3)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.T2_PK) violated

SQL> Select Owner,Constraint_name,Constraint_type,Table_name,Index_name from dba_constraints where table_name='TEST2'

  2  ;

OWNER                          CONSTRAINT_NAME                C TABLE_NAME                     INDEX_NAME

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

SCOTT                                            T2_PK                                               P TEST2                          T2IX

This is easy and Simple.

Any suggestion/Correction are always most welcome.