So, let´s say that I have the following simple example table:
1. cus_id
2. cus_name
3. Other fields . . .
Where "cus_id" is the primary key. And let´s also say that I want
"cus_name" to be unique. I have the option to create a unique constraint or
a unique index. What would be the best decision and why?
Regards,
Jorge Maldonado
On Fri, Oct 4, 2013 at 5:38 PM, David Johnston wrote:
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
