Обсуждение: Multicolumn primary key with null value
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
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