Unable to verify with novalidate option

Question

Hi, I inserted some date into the table. For some reason, I had to disable my restriction. The restriction was related to the index. I used this line of code:

ALTER TABLE my_table DISABLE CONSTRAINT "my_constraint" drop index 

And my_constraint is in a disconnected state. No. I would like to enable this restriction, but after calling this line:

 ALTER TABLE my_table ENABLE NOVALIDATE CONSTRAINT "my_constraint";\ 

I get an error message:

ORA-02299: Failed to confirm (USER.my_constraint) - - duplicate keys found

Answer 1

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

For instance:

 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 

Answer 2

When you inserted the rows into the table, you violated the restriction, it seems that this is a unique restriction based on the error message "detected duplicate keys" error

check which columns the constraint is used on and then run a quick query like the following to see if you have any rows with duplicates (columna and columnb are columns in your unique constraint)

SELECT columna, columnb COUNT () From table HAVING COUNT ()> 1

You cannot enable a unique constraint until all rows in the table comply with the constraint rules.