Re: disallowing multiple NULLs in a unique constraint

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.