Create constraint in alter table without checking existing data

Question

I am trying to create a restriction on the OE.PRODUCT_INFORMATION table that comes with Oracle 11g R2. The restriction should make PRODUCT_NAME unique.

I tried this with the following statement:

ALTER TABLE PRODUCT_INFORMATION ADD CONSTRAINT PRINF_NAME_UNIQUE UNIQUE (PRODUCT_NAME); 

The problem is that OE.PRODUCT_INFORMATION already has product names that currently exist more than two times. Executing the above code causes the following error:

 an alter table validating constraint failed because the table has duplicate key values. 

Is it likely that the newly created constraint will not be used for existing table data? I have already tried the DISABLED keyword. But when I turn on the restriction, I get the same error message.

Answer 1

If you want to use some kind of uniqueness for all future entries, keeping current duplicates, you cannot use the UNIQUE constraint.

You can use a trigger in a table to check the value that you want to insert against the current values ​​of the table, and if it already exists, prevent insertion.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

or you can just remove duplicate values ​​and then restrict the UNIQUE constraint.

EDIT: after the comments by Jonearles and Jeffrey Kemp, I will add that you can indeed include a unique constraint on a table with duplicate values ​​represented using the NOVALIDATE , but you cannot have a unique index on this bounded column.

See Tom Keith's explanation here .

However, I will still worry about how obvious the intentions of future people are to maintain the database. From a support point of view, it would be more obvious to either remove duplicates or use a trigger to make your intention clear. Ymmv

Answer 2

You can create a constraint that will check any newly inserted or updated records, but which will not be checked based on old existing data using the NOVALIDATE keyword, for example:

 ALTER TABLE PRODUCT_INFORMATION ADD CONSTRAINT PRINF_NAME_UNIQUE UNIQUE (PRODUCT_NAME) NOVALIDATE; 

If there is no index in the column, this command will create a unique index in the column.

Answer 3

You can use the delayed version.

 ALTER TABLE PRODUCT_INFORMATION ADD CONSTRAINT PRINF_NAME_UNIQUE UNIQUE (PRODUCT_NAME) deferrable initially deferred NOVALIDATE;