Re: Unique Constraint vs Unique Index

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