I have read about this in the manual. I would like to know if a unique
constraint creates a unique index.

If columns are not allowed to be null, whats the best choice, unique
index or unique constraint?

Thanks
Eric

Some of this may depend upon version [like the case of the introduction
of logical indexes in V10].

Yes it does. So does the declaration of a primary key. And a unique one
too!

I don't know if there is a best choice. At one level they can be used
interchangeably.

But there may be advantages of one over the other in some contexts.

A unique constraint is available for use by foreign key declarations [it is
as much a primary key as a primary key declaration is for this purpose]
if that matters to your question. This is not an option with unique
indexes.

I also believe in recent versions there is no difference in the way
indexes are maintained physically. [The engine logic may behave a little
different if it knows it is working from a table constraint which by
definition is known not to be null ... and thus may not need to do null
checks as often.]

Unique constraints (like pkey declarations) are associated with
the Create/Alter Table statements and cannot be manipulated using
the Create/Alter/Drop Index statements. They are also stored
only in the same dbspace as the table; if that is a factor for you.
As such they are maybe a little simpler to coneptually maintain.

Though I cannot think of a situaiton where I would need to
know any internal details for such a question. Are you asking
because you are looking for a best practice or are looking
into a performance issue?

Personally (for a best practice kind of answer):

I would tend to use the unique constraint (or the primary key
constraint) if I 'believed' this was a requirement of the table
(or more specifically the 'entity' implemented by the table).
That is, if they uniqueness or non-null characteristic is more a
feature of the problem domain for the entity being implemented.

On the other hand I would tend to use an index when this is
something that may reasonably be expected to change in, say,
a future minor re-tuning of the schema (column order, clustering
or partitioning or ... dropped altogether in some cases or added
for other ones) or if I were planning on allowing future changes
of the nullability.

And outside of that, I would take the dbspace requirements,
or any other factors [that might make the alter index statement
the easier tool to work with] into consideration.

Also, for single columns, I would tend to declare those
as column uniqueness constraint (in the create/alter table).
If only to keep the schema a little tighter and possibly better
documented in my view of the problem.


But there is no hard and fast rule on personal or professional practices
[I believe].

"ontsnapt" <eric (AT) cercosoft (DOT) be> wrote