Thread: Unique index VS unique constraint

Il 04/10/2013 18:48, JORGE MALDONADO ha scritto:
> 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.

2 main differences.

First is the meaning: primary key identifies a record. A unique just 
tells you that that value of the record, in the table is unique. If you 
use keys, db structure will be more intelligible (my opinion).

Second one is functional: in an unique constraint you can allow NULL 
values and ignore them. A primary key does not allow this.

> Respectfully,
> Jorge Maldonado

Regards,

Luca.

On 10/04/2013 09:48 AM, 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.

The way I think of it is, that since the SQL standard does not mention 
indices an INDEX (UNIQUE or otherwise) is just the databases application 
of a constraint. So for practical purposes they are the same thing.

>
> Respectfully,
> Jorge Maldonado


-- 
Adrian Klaver
adrian.klaver@gmail.com

On 10/04/2013 10:41 AM, lucazeo@gmail.com wrote:
> Il 04/10/2013 18:48, JORGE MALDONADO ha scritto:
>> 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.
>
> 2 main differences.
>
> First is the meaning: primary key identifies a record. A unique just
> tells you that that value of the record, in the table is unique. If you
> use keys, db structure will be more intelligible (my opinion).

Not sure I follow, you can have a unique index that is not a primary 
key. A primary key is special kind of unique index:

http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html

The primary key constraint specifies that a column or columns of a table 
can contain only unique (non-duplicate), nonnull values. Technically, 
PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but 
identifying a set of columns as primary key also provides metadata about 
the design of the schema, as a primary key implies that other tables can 
rely on this set of columns as a unique identifier for rows.


>
> Second one is functional: in an unique constraint you can allow NULL
> values and ignore them. A primary key does not allow this.
>
>> Respectfully,
>> Jorge Maldonado
>
> Regards,
>
> Luca.
>
>


-- 
Adrian Klaver
adrian.klaver@gmail.com

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.

<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> 

Steve Grey-2 wrote
> 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.

This implies there can be data in the table but not in the index and thus
said index is not part of the model.

This strikes me, though, as a shortcoming of the declarative constraint
implementation since such behavior should not modeled via indexes even if
that is how they are implemented.  The where clause limitation on
constraints is arbitrary though adding it just for this would not pass a
cost-benefit analysis.

David J.




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

On Sat, Oct 5, 2013 at 3:24 PM, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> 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?

Unique constraint would be the right choice, because you want to
constrain the values in the column of your table. The unique index is
just a tool the constrain uses to perform its function. This index
will be created automatically when you add the constraint.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com