Sokrates on Oracle

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 ) ?

About these ads

3 Responses to “Strange ORA-14196”

  1. just guessing: maybe the engine relies on the uniqueness of the index but since only the combination (i, j) is unique this information would be misleading for column i. When I create the unique index on (i, j) before the activation of the constraint the system creates an additional unique index on i. But the system is also happy with a non-unique index on i:

    -- 11.1.0.7
    -- same behaviour for the basic test as in your example
    
    -- 1. index created earlier
    create table strange(i int not null, j int not null);
    create unique index struix on strange ( i, j );
    alter table strange add constraint unique_i unique(i) not deferrable;
    
    select index_name, uniqueness from user_indexes where table_name = 'STRANGE';
    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    UNIQUE_I                       UNIQUE
    STRUIX                         UNIQUE
    
    select index_name, column_name, column_position from user_ind_columns where table_name = 'STRANGE';
    INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
    ------------------------------ ------------------------------ ---------------
    STRUIX                         I                                            1
    STRUIX                         J                                            2
    UNIQUE_I                       I                                            1
    
    -- 2. using a non-unique index on i
    create table strange(i int not null, j int not null);
    create index struix on strange ( i);
    alter table strange add constraint unique_i unique(i) not deferrable;
    
    select index_name, uniqueness from user_indexes where table_name = 'STRANGE';
    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    STRUIX                         NONUNIQUE
    
  2. Dom Brooks said

    Just not allowed.

    Richard Foote mentions here:
    http://richardfoote.wordpress.com/2009/05/19/indexes-and-small-tables-part-vi-loaded/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: