Re: Unique and non unique index

Rama Shankar wrote:
> Hi All,
> We can enforce primary key constraint by using both unique
> and non-unique index. I want to know when to use unique index and when
> non unique.

If you want to enforce a primary key constraint, create a primary key for the table. Oracle will take care of creating the associated index automagically.

If some column other than the PK needs to be unique, create a unique *constraint*, not a unique index. Again, Oracle will take care of creating the associated index automagically.

You can use a unique index to enforce uniqueness but this is sub-optimal. Among other things you can foreign key to a column that has a unique constraint, but you can't foreign key to a column that has it's uniqueness enforced by an index.

If you need an index for performance reasons, create a non-unique index.

//Walt Received on Wed Mar 15 2006 - 08:29:26 CST