Mohamed Houri’s Oracle Notes

April 23, 2009

Unique constraint, non unique index and foreign key index

Filed under: Oracle — hourim @ 1:27 pm

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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: