I'm trying to create a constraint on the OE.PRODUCT_INFORMATION table which is delivered with Oracle 11g R2. The constraint should make the PRODUCT_NAME unique.
I've tried it with the following statement:
ALTER TABLE PRODUCT_INFORMATION
ADD CONSTRAINT PRINF_NAME_UNIQUE UNIQUE (PRODUCT_NAME);
The problem is, that in the OE.PRODUCT_INFORMATION there are already product names which currently exist more than twice. Executing the code above throws the following error:
an alter table validating constraint failed because the table has
duplicate key values.
Is there a possibility that a new created constraint won't be used on existing table data?
I've already tried the DISABLED keyword. But when I enable the constraint then I receive the same error message.
If you are looking to enforce some sort of uniqueness for all future entries whilst keeping your current duplicates you cannot use a UNIQUE constraint.
You could use a trigger on the table to check the value to be inserted against the current table values and if it already exists, prevent the insert.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm
or you could just remove the duplicate values and then enfoce your UNIQUE constraint.
EDIT: After Jonearles and Jeffrey Kemp's comments, I'll add that you can actually enable a unique constraint on a table with duplicate values present using the NOVALIDATE clause but you'd not be able to have a unique index on that constrained column.
See Tom Kyte's explanation here.
However, I would still worry about how obvious the intent was to future people who have to support the database. From a support perspective, it'd be more obvious to either remove the duplicates or use the trigger to make your intent clear. YMMV