5 Replies Latest reply on Jun 6, 2008 12:36 PM by 618435

    Index

    601428
      Can we create a unique index on a column having duplicate values which can enforce the uniqueness for future values.?
        • 1. Re: Index
          229023
          If you mean unique constraints, then you'll be able to enforce only for future records using NOVALIDATE clause.
          • 2. Re: Index
            Frank Kulash
            Sorry, you can't.

            I would create a new column that had distinct values for the existing "grandfathered" duplicates, and a common value for all future rows. This combination of two columns will be unique. Make sure you specify the existing column first, so the index will be usable for the meaningful column.
            • 3. Re: Index
              601428
              thanks for the input guys.
              • 4. Re: Index
                561093
                Hi Arpit,

                Yes, its possible to enforce the Unique constraint to be applied for future values. Here's a small demo:
                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>
                Message was edited by:
                Citrus
                • 5. Re: Index
                  618435
                  Hi,

                  As Citrus mentioned we can enfore unique as well as primary key constraints to check only future records using NOVALIDATE.
                  But you should specify the constarint as INITIALLY DEFERRED DEFERRABLE or DEFERRABLE INITIALLY IMMEDIATE.

                  we can verify the status from dictionary table :
                  SQL> SELECT constraint_type, status, DEFERRABLE, DEFERRED, validated
                    2    FROM user_constraints
                    3   WHERE table_name = 'TEST1';
                  
                  C STATUS   DEFERRABLE     DEFERRED  VALIDATED
                  - -------- -------------- --------- -------------
                  P ENABLED  DEFERRABLE     DEFERRED  NOT VALIDATED
                  
                  1 row selected.