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

by

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.

More Related Contents:

  1. How to convert primary key from integer to serial?
  2. How to reset postgres’ primary key sequence when it falls out of sync?
  3. Postgresql: Conditionally unique constraint
  4. CONSTRAINT to check values from a remotely related table (via join etc.)
  5. How to add “on delete cascade” constraints?
  6. Is there a way to disable function overloading in Postgres
  7. Refresh a materialized view automatically using a rule or notify
  8. How to get function parameter lists (so I can drop a function)
  9. Does a Postgres UNIQUE constraint imply an index?
  10. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  11. Changing primary key int type to serial
  12. Dynamic pivot query using PostgreSQL 9.3
  13. PostgreSQL – disabling constraints
  14. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  15. Reset PostgreSQL primary key to 1
  16. Insert, on duplicate update in PostgreSQL?
  17. How does the search_path influence identifier resolution and the “current schema”
  18. How do you find the row count for all your tables in Postgres
  19. Run batch file with psql command without password
  20. How do you use variables in a simple PostgreSQL script?
  21. How to switch databases in psql?
  22. How to customize the configuration file of the official PostgreSQL Docker image?
  23. How to convert a postgres database to sqlite
  24. Function to loop through and select data from multiple tables
  25. SQL query column does not exist error
  26. SQL Sub queries in check constraint
  27. PostgreSQL 9.3: Dynamic pivot table
  28. Heroku database restore issue
  29. user postgres launches process that takes all CPUs 100% usage
  30. How do I get min, median and max from my query in postgresql?

Leave a Comment