oracle novalidate 出现错误 ora-02299
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17163580/
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
novalidate with error ora-02299
提问by Moudiz
can anybody help with me with this?
有人可以帮我解决这个问题吗？
id | Name
--------
1 | aaa
2 | bbb
3 | aaa
>alter table arc add CONSTRAINT uk_arc UNIQUE (NAME) novalidate
error :ora-02299: cannot validate( .uk_arc ) - duplicate keys found
I am using novalidate to ignore the old duplicate and start to validate all over again.
我正在使用 novalidate 忽略旧的重复项并重新开始验证。
回答by Yasir Arsanukaev
If I get you correctly, you expect Oracle to ignore old duplicate values and allow new values only when they satisfy the constraint. The error is returned because when you add a UNIQUEconstraint, Oracle creates unique index on the column to check the values, but your table already have duplicate values, so it fails. I would create the non-unique index first, then add the constraint so that it uses your existing non-unique index instead of automatically creating the unique index which would fail:
如果我理解正确，您会期望 Oracle 忽略旧的重复值并仅在新值满足约束时才允许它们。返回错误是因为当您添加UNIQUE约束时，Oracle 在列上创建唯一索引来检查值，但您的表已经有重复值，因此失败。我会先创建非唯一索引，然后添加约束，以便它使用您现有的非唯一索引，而不是自动创建会失败的唯一索引：
create index arc_ix on arc (name);
alter table arc add constraint arc_uq unique (name) enable novalidate;
