On Fri, Jul 26, 2013 at 3:19 PM, Alvaro Herrera <alvhe...@2ndquadrant.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...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql