使用错误ora-02299进行验证 [英] novalidate with error ora-02299
问题描述
有人可以帮我吗?
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
我正在使用novalidate来忽略旧的重复项,然后再次开始重新进行验证.
I am using novalidate to ignore the old duplicate and start to validate all over again.
推荐答案
如果我正确地理解了您,您希望Oracle忽略旧的重复值,仅在它们满足约束时才允许新值.返回该错误的原因是,当您添加UNIQUE
约束时,Oracle在该列上创建唯一索引以检查值,但是您的表已经具有重复的值,因此它将失败.我将首先创建非唯一索引,然后添加约束,以便它使用您现有的非唯一索引,而不是自动创建会失败的唯一索引:
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 UNIQUE
constraint, 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:
create index arc_ix on arc (name);
alter table arc add constraint arc_uq unique (name) enable novalidate;
这篇关于使用错误ora-02299进行验证的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!