Unique constraint on a column with duplicate values

oracle

How to create unique constraint on a column with duplicate values,without disturbing the existing data.i have already tried the method below,

To create a normal index first and then create a unique constraint.
But in this I won't be able to drop the unused index created first.i don't want that .

Any other clean methods which can be applied in production env?

Best Answer

I'm sure this has already been asked before, but .. from the Oracle documentation

Further down the page, when you look for what each constraint state is:

VALIDATE | NOVALIDATE The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. Therefore they are described in the context of "ENABLE Clause" and "DISABLE Clause".

ENABLE Clause Specify ENABLE if you want the constraint to be applied to the data in the table.

If you enable a unique or primary key constraint, and if no index exists on the key, then Oracle Database creates a unique index. Unless you specify KEEP INDEX when subsequently disabling the constraint, this index is dropped and the database rebuilds the index every time the constraint is reenabled.

You can also avoid rebuilding the index and eliminate redundant indexes by creating new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.

ENABLE VALIDATE specifies that all old and new data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid.

If any row in the table violates the integrity constraint, then the constraint remains disabled and Oracle returns an error. If all rows comply with the constraint, then Oracle enables the constraint. Subsequently, if new data violates the constraint, then Oracle does not execute the statement and returns an error indicating the integrity constraint violation.

If you place a primary key constraint in ENABLE VALIDATE mode, then the validation process will verify that the primary key columns contain no nulls. To avoid this overhead, mark each column in the primary key NOT NULL before entering data into the column and before enabling the primary key constraint of the table.

ENABLE NOVALIDATE ensures that all new DML operations on the constrained data comply with the constraint. This clause does not ensure that existing data in the table complies with the constraint.

So you would want to specify at the end of the constraint that it is ENABLE NOVALIDATE. Here is an example

ALTER TABLE my_table ADD CONSTRAINT my_constraint UNIQUE (my_columns) ENABLE NOVALIDATE;

Apparently this alone will still throw an ORA-2299 because Oracle is still attempting to back the UNIQUE constraint with a UNIQUE index upon creation. The most simple way to get around that is to create the (non unique) index first.

CREATE INDEX my_index ON my_table (my_columns);
ALTER TABLE my_table ADD CONSTRAINT my_constraint UNIQUE (my_columns) ENABLE NOVALIDATE;
Related Question