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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane Sent: Wednesday, February 28, 2007 1:17 AM To: Jim C. Nasby Cc: John Jawed; pgsql-general@xxxxxxxxxxxxxx Subject: Re: [GENERAL] Difference between UNIQUE constraint vs index "Jim C. Nasby" <jim@xxxxxxxxx> 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.
