在不检查现有数据的情况下在更改表中创建约束
我正在尝试对随 Oracle 11g R2 提供的 OE.PRODUCT_INFORMATION 表创建约束.该限制条件应使 PRODUCT_NAME 独一无二.
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);
问题是，在OE.PRODUCT_INFORMATION 中，已经有产品名称目前存在两次以上.执行上面的代码会抛出以下错误:
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.
是否有可能不会在现有表数据上使用新创建的约束?我已经尝试过 DISABLED 关键字.但是当我启用约束时，我会收到相同的错误消息.
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.
推荐答案
如果您希望在保留当前重复项的同时为所有未来条目强制实施某种唯一性，则不能使用 UNIQUE 约束.
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
或者您可以删除重复的值，然后执行您的 UNIQUE 约束.
or you could just remove the duplicate values and then enfoce your UNIQUE constraint.
在 Jonearles 和 Jeffrey Kemp 发表评论之后，我将补充说，您实际上可以使用 NOVALIDATE 子句在具有重复值的表上启用唯一约束，但您无法在该受限列上具有唯一索引.
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.
参见 Tom Kyte 的解释 这里.
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
相关文章