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
Answer:
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;
or
create index arc_ix on arc (name);
alter table arc add constraint arc_uq unique (name) deferrable enable novalidate;
---------
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
Answer:
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;
or
create index arc_ix on arc (name);
alter table arc add constraint arc_uq unique (name) deferrable enable novalidate;
2299
ReplyDelete