Unique constraint, non unique index and foreign key index

It is well know that if you create a unique constraint on a given table a unique index is automatically created. This automatic index can sometimes not be created. Particularly when the attributes composing the unique constraint are already indexed even within a non unique index. Yes, a unique constraint can be associated to a non unique index. However, for this automatic index creation to be skipped, few conditions should be fulfilled. Let’s illustrate this via simple example

drop table t;

create table t(x number, y varchar2(10), z number);

alter table t add constraint t_uk unique (x);

 

SELECT index_name, table_name, uniqueness

  FROM user_indexes

 WHERE table_name = ‘T’;

 

Index_name	table_name	uniqueness
T_UK	T	UNIQUE

 

A unique index t_uk has been automatically created.

 

Let’s now drop table t and recreate it together with a non unique index on it

drop table t;

create table t(x number, y varchar2(10), z number);

 

CREATE INDEX i_non_uniq ON t(x,y,z);

 

SELECT index_name, table_name, uniqueness

  FROM user_indexes

 WHERE table_name = ‘T’;

 

Index_name	table_name	uniqueness
I_NON_UNIQ	T	NONUNIQUE

 

Create a unique constraint on attribute x

 

ALTER TABLE t ADD CONSTRAINT t_uk UNIQUE (x);

 

And let’s see if a unique index has been automatically created

 

SELECT index_name, table_name, uniqueness

  FROM user_indexes

 WHERE table_name = ‘T’;

 

Index_name	table_name	uniqueness
I_NON_UNIQ	T	NONUNIQUE

 

No, the unique constraint t_uk has been attributed the existing non unique index i_non_uniq. This clearly shows two things (1) a unique constraint is not always followed by the creation of an automatic index (2) a non unique index can cover a unique constraint.  

The second point is not always true. To be true the attribute composing the unique constraint must be on the leading edge of the index. Put is simply, x should be in the beginning of an existing unique or non unique index as it is the case in this example where the leading edge of the i_non_uniq index is x.

 

If now, instead of creating a t_uk unique constraint on x we will create it on y, you will see that Oracle will create a new unique index to cover this unique constraint on y because y is not on the leading edge of the index i_non_uniq

 

DROP TABLE t;

CREATE TABLE t(x NUMBER, y VARCHAR2(10), z NUMBER);

CREATE INDEX i_non_uniq ON t(x,y,z);

 

ALTER TABLE t ADD CONSTRAINT t_uk UNIQUE (y);

 

SELECT index_name, table_name, uniqueness

  FROM user_indexes

 WHERE table_name = ‘T’;

 

Index_name	table_name	uniqueness
T_UK	T	UNIQUE
I_NON_UNIQ	T	NONUNIQUE

 

 

If now, the unique constraint is composed of both y and x in this order, will oracle create an extra unique index or not?

 

DROP TABLE t;

CREATE TABLE t(x NUMBER, y VARCHAR2(10), z NUMBER);

CREATE INDEX i_non_uniq ON t(x,y,z);

 

ALTER TABLE t ADD CONSTRAINT t_uk UNIQUE (y,x); /* in this order y, x */

 

SELECT index_name, table_name, uniqueness

  FROM user_indexes

 WHERE table_name = ‘T’;

 

Index_name	table_name	uniqueness
I_NON_UNIQ	T	NONUNIQUE

 

The extra unique index has not been created despite that the existing index is on x,y,z and the unique constraint is on (y,x). This clearly means that if an existing index has at its leading edge the unique key attributes in any order (y,x) or (x,y) it will cover the unique  constraint and will not create an extra unique index

 

Finally, the same remarks can be extended to the foreign key index. If you have a table T and an index   i_non_uniq on t(x,y,z) and you have implemented a Foreign key on (y,x) or on (x,y), then in this case you don’t have to create an extra index in order to cover your Foreign key and to avoid locking parent table in case of delete.  The i_non_uniq index on t(x,y,z) will cover this for you because it starts with the couple (x,y).