Re: Unique index VS unique constraint

Unique indexes can be partial, i.e. defined with a where clause (that must
be included in a query so that PostgreSQL knows to use that index) whereas
unique constraints cannot.
JORGE MALDONADO wrote
> I have search for information about the difference between "unique index"
> and "unique constraint" in PostgreSQL without getting to a specific
> answer,
> so I kindly ask for an explanation that helps me clarify such concept.

A constraint says what valid data looks like.

An index stores data in such a way as to enhance search performance.

Uniqueness is a constraint. It happens to be implemented via the creation
of a unique index since an index is quickly able to search all existing
values in order to determine if a given value already exists.

PostgreSQL has chosen to allow a user to create a unique index directly,
instead of only via a constraint, but one should not do so. The uniqueness
property is a constraint and so a "unique index" without a corresponding
constraint is an improper model. If you look at the model without any
indexes (which are non-model objects) you would not be aware of the fact
that duplicates are not allowed yet in the implementation that is indeed the
case.

Logically the constraint layer sits on top of an index and performs its
filtering of incoming data so that the index can focus on its roles of
storing and retrieving. Extending this thought the underlying index should
always be non-Unique and a unique filter/constraint would use that index for
validation before passing the new value along. However, practicality leads
to the current situation where the index takes on the added role of
enforcing uniqueness. This is not the case for any other constraint but the
UNIQUE constraints case is so integral to PRIMARY KEY usage that the special
case behavior is understandable and much more performant.

Conceptually the index is an implementation detail and uniqueness should be
associated only with constraints.

David J.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773427.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

--
Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql