Thread

1. disallowing multiple NULLs in a unique constraint

   David Garamond <lists@zara.6.isreserved.com> — 2004-02-09T05:06:41Z

   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
   
   
   

2. Re: disallowing multiple NULLs in a unique constraint

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-02-09T05:57:12Z

   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
   
   

3. Re: disallowing multiple NULLs in a unique constraint

   David Garamond <lists@zara.6.isreserved.com> — 2004-02-09T13:19:21Z

   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
   
   
   

4. Re: disallowing multiple NULLs in a unique constraint

   Peter Eisentraut <peter_e@gmx.net> — 2004-05-12T11:00:06Z

   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.
   
   

5. Re: disallowing multiple NULLs in a unique constraint

   scott.marlowe <scott.marlowe@ihs.com> — 2004-05-12T14:00:40Z

   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.