disallowing multiple NULLs in a unique constraint

David Garamond <lists@zara.6.isreserved.com> writes:

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.

regards, tom lane

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

Am Montag, 9. Februar 2004 06:06 schrieb David Garamond:

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? Can a CHECK constraint does that? Will a trigger with
SELECT count(*) ... WHERE f IS NULL be too slow if the table is large?

You can time that yourself (but I suspect it won't be pretty), but I think
that you're going to have all kinds of other problems if you base your
database schema on the assumption that NULL = NULL. Better fix the
application.

On Wed, 12 May 2004, Peter Eisentraut wrote:

Am Montag, 9. Februar 2004 06:06 schrieb David Garamond:

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? Can a CHECK constraint does that? Will a trigger with
SELECT count(*) ... WHERE f IS NULL be too slow if the table is large?

You can time that yourself (but I suspect it won't be pretty), but I think
that you're going to have all kinds of other problems if you base your
database schema on the assumption that NULL = NULL. Better fix the
application.

It's important to make the point here that SQL Server is broken in this
respect, both in terms of set theory, and the SQL spec definition of how
NULLs behave.

The better option would be to use something other than NULL for this.
I.e. use the characters 'N/A' or something.