Re: disallowing multiple NULLs in a unique constraint

Tom Lane wrote:
>>SQL Server only allow one NULL in a unique constraint column (it's the
>>unique index that does that, so the unique constraint behaves like that
>>too). The question is, what is the best way to simulate that behaviour
>>in Postgres?
>
> The best way is to rewrite your app to not depend on nonstandard
> semantics.  SQL Server is unquestionably violating the SQL spec here,
> and it's not out of the question that Microsoft might realize that and
> fix it, leaving you up the creek on that platform as well as Postgres.
>
> Instead of using NULL in that fashion, perhaps you could choose a
> non-null dummy value to use instead.

Thanks, Tom. Yeah, that answer was expected :-) DB2 is also violating
specs here, though they have a syntax (UNIQUE WHERE NOT NULL or something).

Btw, one example case: a table containing a tree with adjacency list.
The root node is the one that doesn't have a parent (parent_id is NULL).
parent_id REFERENCES thetable(id). How do we restrict the table to have
only one root node?

--
dave