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
thecolumns included in the constraint are equal. However, null values are not considered equal in this comparison. That
meanseven in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at
leastone 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
notconsidered equal.
and further,
> PostgreSQL automatically creates a unique index when a unique constraint or a primary key is defined for a table. The
indexcovers the columns that make up the primary key or unique columns (a multicolumn index, if appropriate), and is
themechanism 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