Why can I create a table with PRIMARY KEY on a nullable column?

Question

The following code creates a table without raising any errors:

CREATE TABLE test(
ID INTEGER NULL,
CONSTRAINT PK_test PRIMARY KEY(ID)
)

Note that I cannot insert a NULL, as expected:

INSERT INTO test
VALUES(1),(NULL)
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null).
********** Error **********

ERROR: null value in column "id" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null).

Why can I create a table with a self-contradictory definition? ID column is explicitly declared as NULLable, and it is implicitly not nullable, as a part of the PRIMARY KEY. Does it make sense?

Edit: would it not be better if this self-contradictory CREATE TABLE just failed right there?

Answer 1

Because the PRIMARY KEY makes the included column(s) NOT NULL automatically. I quote the manual here:

The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL.

Bold emphasis mine.

I ran a test to confirm that NOT NULL is completely redundant in combination with a PRIMARY KEY constraint (in the current implementation, retested in version 13). The NOT NULL constraint stays even after dropping the PK constraint, irregardless of an explicit NOT NULL clause at creation time.

CREATE TABLE foo (foo_id int PRIMARY KEY);
ALTER TABLE foo DROP CONSTRAINT foo_pkey;

db=# \d foo
   table »public.foo«
 column |  type   | attribute
--------+---------+-----------
 foo_id | integer | not null    -- stays

db<>fiddle here

Identical behavior if NULL is included in the CREATE TABLE statement.

It still won't hurt to keep NOT NULL redundantly in code repositories if the column is supposed to be NOT NULL. If you later decide to alter the PK constraint, you might forget to mark the column NOT NULL - or whether it even was supposed to be NOT NULL.

There is an item in the Postgres TODO wiki to decouple NOT NULL from the PK constraint. So this might change in future versions:

Move NOT NULL constraint information to pg_constraint

Currently NOT NULL constraints are stored in pg_attribute without any designation of their origins, e.g. primary keys. One manifest problem is that dropping a PRIMARY KEY constraint does not remove the NOT NULL constraint designation. Another issue is that we should probably force NOT NULL to be propagated from parent tables to children, just as CHECK constraints are. (But then does dropping PRIMARY KEY affect children?)

Answer to added question

Would it not be better if this self-contradictory CREATE TABLE just failed right there?

As explained above, this

foo_id INTEGER NULL PRIMARY KEY

is (currently) 100 % equivalent to:

foo_id INTEGER PRIMARY KEY

Since NULL is treated as noise word in this context.
And we wouldn't want the latter to fail. So this is not an option.

Answer 2

If memory serves, the docs mention that:

• the null in create table statements is basically a noise word that gets ignored
• the primary key forces a not null and a unique constraint

See:

# create table test (id int null primary key);
CREATE TABLE
# \d test
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)