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 <pol...@yahoo.com> 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 >