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 not implicitly 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?

Laws

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, regardless 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.

Kettie

Nice nuance about "later drop the primary key" – Laws

@AlexKuznetsov: Actually I ran a test and proved myself wrong for this detail. The rest applies, however. See amended answer. – Kettie

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)

Bondon

Answer 3

If as @ErwinBrandstetter said, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, you can use an UNIQUE constraint without NOT NULL instead of PRIMARY KEY. Example:

CREATE TABLE test(
    id integer,
    CONSTRAINT test_id_key UNIQUE(id)
);

This way you can do things like:

INSERT INTO test (id) VALUES (NULL);
INSERT INTO test (id) VALUES (NULL);
INSERT INTO test (id) VALUES (NULL);

Mauriciomaurie

Answer 4

A PRIMARY KEY column is forced to be NOT NULL.

The documentation says as shown below:

Adding a primary key will automatically create a unique B-tree index on the column or group of columns listed in the primary key, and will force the column(s) to be marked NOT NULL.

Clarhe

Answer 5

Speaking about NOT NULL, there are many ways to ensure it. Not speaking only about PostgreSQL as a relational database engine:

1. Column constraint.
2. Table constraint (single NOT NULL or complex Boolean expression)
3. Index definition,
4. Triggers that change any NULL to "something else". There may be even other methods.

One suffices. Not having the others means we have a contradiction? I do not think so.

Rhino