Thread: multicolumn PRIMARY KEY introduces wrong 'not null' fields

Laurent Martelli (laurent@bearteam.org) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
multicolumn PRIMARY KEY introduces wrong 'not null' fields

Long Description
If you have a primary key on several columns, each of these columns is given the 'not null' modifier. I can't see why
thisis required. In the example below, the 'Type' column is made 'not null'. 

Sample Code
CREATE TABLE test (
        Type integer,
        PictureID integer NOT NULL REFERENCES pictures(PictureID),
        Value character varying(128) NOT NULL,
    PRIMARY KEY (Type,PictureID,Value));


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> If you have a primary key on several columns, each of these columns is
> given the 'not null' modifier. I can't see why this is required.

(A) it makes no sense otherwise, and (B) the SQL spec says so:

         A unique constraint is satisfied if and only if no two rows in
         a table have the same non-null values in the unique columns. In
         addition, if the unique constraint was defined with PRIMARY KEY,
         then it requires that none of the values in the specified column or
         columns be the null value.

If you don't want NOT NULL, maybe what you are after is a plain UNIQUE
constraint, not PRIMARY KEY.

            regards, tom lane