Re: Multicolumn primary key with null value

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