Re: Difference between UNIQUE constraint vs index

On Tue, Feb 27, 2007 at 06:43:51PM -0600, John Jawed wrote:
> Is there any difference as far as when the "uniqueness" of values is
> checked in DML between a unique index vs a unique constraint? Or is
> the only difference syntax between unique indices and constraints in
> PostgreSQL?

Syntax only, AFAIK. I prefer using constraints if I actually want to
constrain the data; it makes it clear that it's a restriction.

In some databases if you know that an index just happens to be unique
you might gain some query performance by defining the index as unique,
but I don't think the PostgreSQL planner is that smart. There can also
be some additional overhead involved with a unique index (vs
non-unique), such as when two backends try and add the same key at the
same time (one of them will have to block).
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)