"create unique index" vs "add constraint unique" 4

[misterstick]

please forgive my dumbness, but what's the difference between

create unique index index_name
on table_name
...;

and

alter table table_name add 
constraint index_name unique
...;

mr s. <

 

[Dagon]

It's mainly a historical difference. Unique indexes were around before constraints were created, so Oracle had to keep the syntax of the "create unique index" command for backward compatibility. It is therefore possible to create a unique index without it also being a unique constraint.

When constraints came along, the "alter table add constraint.." command was created. However, a unique constraint is implemented using a unique index. Therefore, you can create your unique index and then enforce the unique constraint using the command:

alter table xxx add constraint ... unique ... using index ...;

If you don't pre-create the unique index, it will create a unique index behind the scenes.

So I suppose to summarize:

a) you can have a unique index without it being a unique constraint.
b) a unique constraint does require a unique index. The index can either be created separately or created as a byproduct of creating the constraint.

 

[Thargy]

Stick,

as usual Dagon has the right of it. I would however add a couple of points to the above.

You should never create a unique index to enforce a unique constraint because it is not self-evident that the DBA really meant a unique constraint. Uniqueness should always be achieved by creating a constraint, so that the intent is obvious.

If the db automatically creates an index "behind the scenes" well and good. However, if a suitable index already exists, oracle will automatically use it, without creating another. Coercing the creation of an unnecessary unique index is a waste of time and effort.

If you have a unique constraint on a column, then it may be used in referential integrity constraints (i.e. foreign keys). You can't create a foreign key to an index.

If in the future Oracle corporation decided to use some means other than indexes to enforce uniqueness, then a unique constraint would continue to work, whereas an index might not. This is incredibly unlikely, but it is (IMHO) a good practice to go with what the manufacturer intended, rather than try to cajole a system into doing something 'clever'.

Regards

T

Grinding away at things Oracular

 

[misterstick]

wow. concise, informative, interesting. 3/3.

many thanks, and stars for everyone.



mr s. <

 

[jimirvine]

Minor point of order

a unique constraint does require a unique index.

This is not actually true. A unique constraint can happily work away with a non unique index (the structure of both is internally the same anyway). In fact, Oracle will automatically create non-unique index if you define the constraint as deferrable. In addition, if you need to allow a constraint to be in the state of 'ENABLE NOVALIDATE' a non-unique index is required for this.