Обсуждение: Unique Constraint vs Unique Index
Hi,
I have been reading about "unique constraint" and "unique index" in PostgreSQL documentation and also did some search in Google. However, it is not very clear to me which one to use or when to use one or the other. I understand that a "unique constraint" creates a "unique index" under the hood. So, at the end, both will check for duplicate records based on the field(s) specified for the unique constraint and the unique index.
What would be a guideline about when to use one or the other?
Respectfully,
Jorge Maldonado
On 2020-May-05, JORGE MALDONADO wrote: > I have been reading about "unique constraint" and "unique index" in > PostgreSQL documentation and also did some search in Google. However, it is > not very clear to me which one to use or when to use one or the other. I > understand that a "unique constraint" creates a "unique index" under the > hood. So, at the end, both will check for duplicate records based on the > field(s) specified for the unique constraint and the unique index. > > What would be a guideline about when to use one or the other? The unique constraint is a type of object that's defined by the SQL standard. The unique index is an implementation artifact that we use to implement the constraints. You can use unique indexes with additional features that you cannot create constraints for, such as partial indexes. Other than that, they're supposed to be pretty much the same. I would suggest to use constraints rather than indexes whenever possible. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2020-May-05, JORGE MALDONADO wrote: > I have been reading about "unique constraint" and "unique index" in > PostgreSQL documentation and also did some search in Google. However, it is > not very clear to me which one to use or when to use one or the other. I > understand that a "unique constraint" creates a "unique index" under the > hood. So, at the end, both will check for duplicate records based on the > field(s) specified for the unique constraint and the unique index. > > What would be a guideline about when to use one or the other? BTW you already asked this in October 2013 and got good answers. https://postgr.es/m/CAAY=A78oJ--0aPm4E6ASJ32Z3UHk0nOqHQ_SvrtjOmq7i=UKDQ@mail.gmail.com You had already asked in July 2013, too. https://postgr.es/m/CAAY=A78V1gCxC6VEU4_G8e0XouT=c64RA2GaSfGE8MEy3=33_g@mail.gmail.com -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services