Create constraint in alter table without checking existing data
Solution 1
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
Solution 2
You can certainly create a constraint which will validate any newly inserted or updated records, but which will not be validated against old existing data, using the NOVALIDATE
keyword, e.g.:
ALTER TABLE PRODUCT_INFORMATION
ADD CONSTRAINT PRINF_NAME_UNIQUE UNIQUE (PRODUCT_NAME)
NOVALIDATE;
If there is no index on the column, this command will create a non-unique index on the column.
mhmpl
Updated on June 03, 2020Comments
-
mhmpl almost 4 years
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.