sql - Cannot validate, with novalidate option

Question

Hi I was inserting some date do my table. For some reasons I had to disable my constraint. The constraint was associated with index. I. ve used this line of code:

ALTER TABLE my_table DISABLE CONSTRAINT "my_constraint" drop index

And my_constraint is in disable state. No I' d like to enable this constraint, but after calling this line:

ALTER TABLE my_table ENABLE NOVALIDATE CONSTRAINT "my_constraint";

I recive an error:

ORA-02299: cannot validate (USER.my_constraint) - - duplicate keys found

Answer 1

You cannot have non-unique values with a unique index. But you can have non-unique values with a unique constraint that is enforced by a non-unique index. Even if you initially created a non-unique index, the drop index and enable syntax will try to recreate a unique index unless you provide more details in the using index section.

For example:

SQL> create table my_table(my_column number,
  2     constraint my_constraint unique (my_column));

Table created.

SQL> alter table my_table disable constraint my_constraint drop index;

Table altered.

SQL> insert into my_table select 1 from dual union all select 1 from dual;

2 rows created.

SQL> alter table my_table enable novalidate constraint my_constraint;
alter table my_table enable novalidate constraint my_constraint
*
ERROR at line 1:
ORA-02299: cannot validate (USER.MY_CONSTRAINT) - duplicate keys found


SQL> alter table my_table enable novalidate constraint my_constraint
  2     using index (create index my_index on my_table(my_column));

Table altered.

SQL> --The constraint is enforced, even though other rows violate it.
SQL> insert into my_table values(1);
insert into my_table values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (USER.MY_CONSTRAINT) violated