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.
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,
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.
and further,
PostgreSQL automatically creates a unique index when a unique constraint or a
primary key is defined for a table. The index covers the columns that make up
the primary key or unique columns (a multicolumn index, if appropriate), and is
the mechanism that enforces the constraint.
So they are "the same" in pg, and you don't syntactically need a NOT NULL
constraint on the column(s) involved.
--
Lew
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
