Sokrates on Oracle

Archive for October, 2013

Strange ORA-14196

Posted by Matthias Rogel on 7. October 2013

It seems that sometimes you need a non-unique index to enforce a unique constraint even if this constraint is declared as not deferrable.

sokrates@11.2 > create table strange(i int not null, j int not null);

Table created.

sokrates@11.2 > alter table strange add constraint unique_i unique(i) not deferrable
  2  using index ( create unique index struix on strange ( i, j ) )
  3  /
alter table strange add constraint unique_i unique(i) not deferrable
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

WTF ?
We have to create a non-unique index here !

sokrates@11.2 > alter table strange add constraint unique_i unique(i) not deferrable
  2  using index ( create  index struix on strange ( i, j ) )
  3  /

Table altered.

Also reproduced on 12.1.
Who can explain this behaviour to me ( I suppose it is a bug ) ?

Posted in Allgemein | Tagged: | 3 Comments »