Обсуждение: Multicolumn primary key with null value

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
keycolumns? 
>
> regards
> Szymon Guz

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

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

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