oracle 在不检查现有数据的情况下在更改表中创建约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7993585/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create constraint in alter table without checking existing data
提问by mhmpl
I'm trying to create a constraint on the OE.PRODUCT_INFORMATIONtable which is delivered with Oracle 11g R2. The constraint should make the PRODUCT_NAMEunique.
我正在尝试在Oracle 11g R2 提供的OE.PRODUCT_INFORMATION表上创建约束。该约束应使PRODUCT_NAME唯一。
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_INFORMATIONthere are already product names which currently exist more than twice. Executing the code above throws the following error:
问题是，在OE.PRODUCT_INFORMATION中已经有产品名称当前存在两次以上。执行上面的代码会抛出以下错误：
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 DISABLEDkeyword. But when I enable the constraint then I receive the same error message.
是否有可能不会在现有表数据上使用新创建的约束？我已经尝试过DISABLED关键字。但是当我启用约束时，我会收到相同的错误消息。
采纳答案by Ollie
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.
如果您希望在保留当前重复项的同时为所有未来条目强制实施某种唯一性，则不能使用 UNIQUE 约束。
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
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.
或者您可以删除重复的值，然后执行您的 UNIQUE 约束。
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 NOVALIDATEclause but you'd not be able to have a unique index on that constrained column.
编辑：在 Jonearles 和 Jeffrey Kemp 发表评论之后，我将补充说，您实际上可以使用该NOVALIDATE子句在具有重复值的表上启用唯一约束，但您无法在该受约束的列上拥有唯一索引。
See Tom Kyte's explanation here.
在这里查看 Tom Kyte 的解释。
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
但是，我仍然会担心这种意图对未来必须支持数据库的人来说有多明显。从支持的角度来看，删除重复项或使用触发器来明确您的意图会更明显。青年会
回答by Jeffrey Kemp
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 NOVALIDATEkeyword, e.g.:
您当然可以使用NOVALIDATE关键字创建一个约束来验证任何新插入或更新的记录，但不会针对旧的现有数据进行验证，例如：
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.
如果列上没有索引，此命令将在列上创建一个非唯一索引。
回答by Judy Wang
You can use deferrable .
您可以使用 deferable 。
ALTER TABLE PRODUCT_INFORMATION
ADD CONSTRAINT PRINF_NAME_UNIQUE UNIQUE (PRODUCT_NAME)
deferrable initially deferred NOVALIDATE;
