Thread: Unique index and unique constraint
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.
Respectfully,
Jorge Maldonado
I try to explain my point of view, also in my not so good English:
A primary key is defined by dr. Codd in relational model.2013/7/26 JORGE MALDONADO <jorgemal1960@gmail.com>
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.Respectfully,Jorge Maldonado
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. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Jul 26, 2013 at 3:19 PM, Alvaro Herrera <alvherre@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.ru@gmail.com
2013/7/27 Alvaro Herrera <alvherre@2ndquadrant.com>
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.
Note, partial uniqueness can be achieved by using EXCLUDE contraints also.
// Dmitriy.