Re: Difference between UNIQUE constraint vs index

SQLite, MySQL, and MS Access each use indexes for unique constraints.

Doesn't the SQL spec specify that CREATE INDEX can be used to create
UNIQUE indexes? Are there any real systems that don't support indexes
but that support unique? It seems silly, since the code for a primary
key is a superset of what's needed for unique, so I would expect only
legacy systems to support non-indexed uniques. Any newer DBMS would
implement primary keys and then steal the code for uniques.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Wednesday, February 28, 2007 1:17 AM
To: Jim C. Nasby
Cc: John Jawed; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Difference between UNIQUE constraint vs index

"Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> 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.

Actually, the planner only pays attention to whether indexes are unique;
the notion of a unique constraint is outside its bounds. In PG a unique
constraint is implemented by creating a unique index, and so there is
really not any interesting difference.

I would imagine that other DBMSes also enforce uniqueness by means of
indexes, because it'd be awful darn expensive to enforce the constraint
without one; but I'm only guessing here, not having looked. Can anyone
point to a real system that enforces unique constraints without an
underlying index?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--------------------------------------------------------------------
** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged,
confidential or proprietary information. If you are not the intended
recipient(s), or the employee or agent responsible for delivery of
this message to the intended recipient(s), you are hereby notified
that any dissemination, distribution or copying of this e-mail
message is strictly prohibited. If you have received this message in
error, please immediately notify the sender and delete this e-mail
message from your computer.