On May 13, 2007, at 17:21 , Lew wrote:
Peter Childs wrote:
Apart from anything a unique constraint is NOT the same as a
unique index, as you need a not null constraint on the column as
well.
Not true, whichever way 'round you meant it.
Technically, the UNIQUE constraint is a logical concept which is
physically implemented in PostgreSQL via a unique BTREE index. Since
there is only one way to implement a UNIQUE constraint in PostgreSQL,
the two concepts are very closely tied. However, say one day
PostgreSQL as a unique GiST index implementation. Then there are two
potentially two physical implementations for the UNIQUE constraint.
For pg unique constraint
<http://www.postgresql.org/docs/8.1/interactive/ddl-
constraints.html#AEN2016>
In general, a unique constraint is violated when there are two or
more rows in the table where the values of all of the columns
included in the constraint are equal. However, null values are not
considered equal in this comparison. That means even in the
presence of a unique constraint it is possible to store duplicate
rows that contain a null value in at least one of the constrained
columns. This behavior conforms to the SQL standard,
Note here, there is no mention of indexes (a implementation issue):
just the logical constraints.
unique index
<http://www.postgresql.org/docs/8.1/interactive/indexes-unique.html>
When an index is declared unique, multiple table rows with equal
indexed values will not be allowed. Null values are not considered
equal.
Here, they're making the distinction between unique and non-unique
(BTREE) indexes: implementation.
These are subtle points, but worth distinguishing.
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
