Thread

1. Multicolumn primary key with null value

   Szymon Guz <mabewlun@gmail.com> — 2010-04-22T17:34:38Z

   Does any SQL standard allows for a multicolumn primary key where in one
   record there is a null in on of the primary key columns?
   
   regards
   Szymon Guz
   

2. Re: Multicolumn primary key with null value

   Said Ramirez <sramirez@vonage.com> — 2010-04-22T17:42:16Z

   Primary keys are defined as 'unique not null' even if they are 
   composite. So I believe postgres would not let you do that:
   
   5.3.4. Primary Keys
   
   Technically, a primary key constraint is simply a combination of a 
   unique constraint and a not-null constraint.
   ....
   
   A primary key indicates that a column or group of columns can be used as 
   a unique identifier for rows in the table. (This is a direct consequence 
   of the definition of a primary key. Note that a unique constraint does 
   not, by itself, provide a unique identifier because it does not exclude 
   null values.) This is useful both for documentation purposes and for 
   client applications.
   
   http://www.postgresql.org/docs/8.1/static/ddl-constraints.html
   
   
   Szymon Guz wrote:
   > Does any SQL standard allows for a multicolumn primary key where in one record there is a null in on of the primary key columns?
   > 
   > regards
   > Szymon Guz
   
   

3. Re: Multicolumn primary key with null value

   Craig Ringer <craig@postnewspapers.com.au> — 2010-04-23T01:27:29Z

   On 23/04/2010 1:42 AM, Said Ramirez wrote:
   > Primary keys are defined as 'unique not null' even if they are
   > composite. So I believe postgres would not let you do that
   
   You can, however, add a UNIQUE constraint on the column set as a whole. 
   PostgreSQL does *not* enforce non-null in this case, so some or all of 
   any fields not constrained NOT NULL are permitted to be NULL.
   
   *however*, it might not do what you want. Because "NULL = NULL" has the 
   result "NULL", not "true", the following is quite legal:
   
   create table test (
      a text not null,
      b text,
      unique(a,b)
   );
   
   insert into test (a,b) values ('fred',NULL);
   insert into test (a,b) values ('fred',NULL);
   
   
   ... and will succeed:
   
   db=> select * from test;
      a   |  b
   ------+-----
     fred |
     fred |
   
   
   
   If you wish to prohibit this, then you can't really use nullable fields 
   in the unique constraint. You'll have to do something ugly like define 
   an explicit 'none/undefined' placeholder value, or re-think how you're 
   storing things.
   
   It's for this reason that I think it's a really good thing that PRIMARY 
   KEY requires all fields in the key to be NOT NULL. SQL NULLs just don't 
   make sense in a primary key because they don't test equal to another null.
   
   --
   Craig Ringer
   
   

4. Re: Multicolumn primary key with null value

   Adrian von Bidder <avbidder@fortytwo.ch> — 2010-04-23T07:50:43Z

   On Friday 23 April 2010 03.27:29 Craig Ringer wrote:
   > insert into test (a,b) values ('fred',NULL);
   > insert into test (a,b) values ('fred',NULL);
   > 
   > 
   > ... and will succeed:
   
   Hmm.  Perhaps not as ugly as "none" placeholders:
   
   create unique index on test (b) where a is null;
   create unique index on test (a) where b is null;
   
   cheers
   -- vbi
   
   -- 
   Protect your privacy - encrypt your email: http://fortytwo.ch/gpg/intro
   

5. Re: Multicolumn primary key with null value

   Craig Ringer <craig@postnewspapers.com.au> — 2010-04-23T08:43:48Z

   On 23/04/10 15:50, Adrian von Bidder wrote:
   > On Friday 23 April 2010 03.27:29 Craig Ringer wrote:
   >> insert into test (a,b) values ('fred',NULL);
   >> insert into test (a,b) values ('fred',NULL);
   >>
   >>
   >> ... and will succeed:
   >
   > Hmm.  Perhaps not as ugly as "none" placeholders:
   >
   > create unique index on test (b) where a is null;
   > create unique index on test (a) where b is null;
   
   True ... and Pg can even use them both together for bitmap index scans, 
   albeit not as efficiently as a single multicolumn index.
   
   This really isn't viable for >2 nullable fields, though, as the number 
   of indexes increases to impractical levels rather quickly.
   
   --
   Craig Ringer