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:

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