CODEWITHSUNDEEP
oracle-databaseplsqldeveloper
Neeraj
Neeraj

Reputation: 11

Alter table enable novalidate constraint

I am trying to add UNIQUE KEY on a previously existing table with duplicate records by setting ENABLE NOVALIDATE.

But I am getting ORA-02299: cannot validate (my_owner.my_key_UK ) - duplicate keys found

ALTER TABLE my_owner.my_table
ADD CONSTRAINT my_key_UK UNIQUE (ID1,ID2)
ENABLE NOVALIDATE;

Upvotes: 1

Views: 33419

Answers (2)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

Oracle ensure unique values using indexes. And if you create unique constrains db automatic creates unique index. Workaround is add DEFERRABLE options. In this case oracle creates normal index. Check example. 

create table table_abc (a number,b number);

insert into table_abc values(1,1);
insert into table_abc values(1,2);

ALTER TABLE table_abc ADD CONSTRAINT my_key_a UNIQUE (a) DEFERRABLE enable novalidate; -- no error but in table nonunique values
ALTER TABLE table_abc ADD CONSTRAINT my_key_b UNIQUE (b) ENABLE NOVALIDATE; --no error 

select * from user_indexes where table_name ='TABLE_ABC';

Upvotes: 0

APC
APC

Reputation: 146209

A unique constraint uses an index to enforce the noduplicates rule. By default it will create a unique index (makes sense right?). It is this index creation which is hurling ORA-02299.

However, if this is an existing index on the constrained columns the constraint will use that. The good news is, the index doesn't need to be unique for the constraint to use it.

So what you need to do is build a non-unique index first:

create index whatever_idx on my_table (ID1,ID2);

Then you will be able to create your constraint:

ALTER TABLE my_owner.my_table
ADD CONSTRAINT my_key_UK UNIQUE (ID1,ID2)
ENABLE NOVALIDATE;

You can check this by querying the data dictionary:

select uc.constraint_name
       , uc.constraint_type
       , uc.index_name
       , ui.uniqueness as idx_uniqueness
from user_constraints uc
     join user_indexes ui
          on ui.index_name = uc.index_name
where uc.table_name = 'MY_TABLE'

Upvotes: 7

Related Questions