Re: Unique index and unique constraint

On Fri, Jul 26, 2013 at 3:19 PM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> JORGE MALDONADO escribió:
>> I guess I am understanding that it is possible to set a unique index or a
>> unique constraint in a table, but I cannot fully understand the difference,
>> even though I have Google some articles about it. I will very much
>> appreciate any guidance.
>
> The SQL standard does not mention indexes anywhere. Therefore, in the
> SQL standard world, the way to define uniqueness is by declaring an
> unique constraint. Using unique constraints instead of unique indexes
> means your code stays more portable. Unique constraints appear in
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS, whereas unique indexes do not.
>
> PostgreSQL implements unique constraints by way of unique indexes (and
> it's likely that all RDBMSs do likewise). Also, the syntax to declare
> unique indexes allows for more features than the unique constraints
> syntax. For example, you can have a unique index that covers only
> portion of the table, based on a WHERE condition (a partial unique
> index). You can't do this with a constraint.

Also, AFAIU, one can defer the uniqueness check until the end of
transaction if it is constraint, and can not it it is unique index.
Correct?

http://www.postgresql.org/docs/9.2/static/sql-set-constraints.html

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray(dot)ru(at)gmail(dot)com