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