Thread: disallowing multiple NULLs in a unique constraint
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? -- dave
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.