Enable novalidate Unique constraint 1

[MikeJones]

Hi all,

I'm tring to create a unique constraint in enable novalidate mode. Here is the problem I'm getting.

I can create the constraint in disabled mode OK.

alter table my_table add constraint constraint_name UNIQUE
( col1,
col2,
col3,
col4,
col5,
col65
) disable
/

This creates OK, but when I then try and enable the constraint in novalidate mode I get the following...

alter table my_table ENABLE NOVALIDATE CONSTRAINT constraint_name
/

ERROR at line 1:
ORA-02299: cannot validate (schema.constraint_name) - duplicate keys found

I know there are duplicate keys in the table, which I why I am using the novalidate clause so that it does not validate current data only new DML.

I've read the Oracle documentation and it doesn't seem to rule out what I am trying to do, does anyone know where I aqm going wrong? If it's not possible to create this type of constraint is there a workaround so I can still do the same enforcement in a different way?

Thanks in advance,

Mike.

 

[sem]

The trick is to enable constraint using other then default(unique) index, because when enabling unique/primary constraints Oracle always builds a unique index even with novalidate clause.

 

[MikeJones]

Thanks Sem, But I don't understand? How can I enable this constraint without a unique clause and still have it as unique? Could you provide an example?

Thanks,

Mike.

 

[sem]

When unique constraint is disabled you should create another, nonunique index on the same columns set.
When enabling constraint Oracle will not build another index but rather use existing.

SQL> create table a (a number , unique(a));
SQL> alter table a disable constraint SYS_C0011998;
SQL> create index a_ind on a(a);
SQL> insert into a values(1);
SQL> insert into a values(1);
SQL> commit;
SQL> alter table a enable novalidate constraint SYS_C0011998;

I've omited sql*plus feedbacks and for they're in Russian

 

[MikeJones]

Marvelous!!

I guess that was the bit I didn't get from the documentation, needing another non-unique index. A bit of a shame that I have to have two indexes on the same thing, but I can more than live with that!!

Thanks again,

Mike.