Re: Unique index VS unique constraint - Mailing list pgsql-sql

<p dir="ltr">Unique indexes can be partial, i.e. defined with a where clause (that must be included in a query so that
PostgreSQLknows to use that index) whereas unique constraints cannot.<br /><div class="gmail_quot<blockquote class="
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">JORGE MALDONADO wrote<br /> > I have search for
informationabout the difference between "unique index"<br /> > and "unique constraint" in PostgreSQL without getting
toa specific<br /> > answer,<br /> > so I kindly ask for an explanation that helps me clarify such concept.<br
/><br/> A constraint says what valid data looks like.<br /><br /> An index stores data in such a way as to enhance
searchperformance.<br /><br /> Uniqueness is a constraint.  It happens to be implemented via the creation<br /> of a
uniqueindex since an index is quickly able to search all existing<br /> values in order to determine if a given value
alreadyexists.<br /><br /> PostgreSQL has chosen to allow a user to create a unique index directly,<br /> instead of
onlyvia a constraint, but one should not do so.  The uniqueness<br /> property is a constraint and so a "unique index"
withouta corresponding<br /> constraint is an improper model.  If you look at the model without any<br /> indexes
(whichare non-model objects) you would not be aware of the fact<br /> that duplicates are not allowed yet in the
implementationthat is indeed the<br /> case.<br /><br /> Logically the constraint layer sits on top of an index and
performsits<br /> filtering of incoming data so that the index can focus on its roles of<br /> storing and retrieving.
 Extendingthis thought the underlying index should<br /> always be non-Unique and a unique filter/constraint would use
thatindex for<br /> validation before passing the new value along.  However, practicality leads<br /> to the current
situationwhere the index takes on the added role of<br /> enforcing uniqueness.  This is not the case for any other
constraintbut the<br /> UNIQUE constraints case is so integral to PRIMARY KEY usage that the special<br /> case
behavioris understandable and much more performant.<br /><br /> Conceptually the index is an implementation detail and
uniquenessshould be<br /> associated only with constraints.<br /><br /> David J.<br /><br /><br /><br /><br /><br />
--<br/> View this message in context: <a
href="http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773427.html"
target="_blank">http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773427.html</a><br
/>Sent from the PostgreSQL - sql mailing list archive at Nabble.com.<br /><br /><br /> --<br /> Sent via pgsql-sql
mailinglist (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></div>