EC
1171
MySQLERRORCommonDDLHIGH confidence

All parts of a PRIMARY KEY must be NOT NULL

Production Risk

Low — DDL fails; no data loss.

What this means

ER_NULL_COLUMN_IN_INDEX (1171, SQLSTATE 42000) is raised when a column defined as nullable (without NOT NULL) is included in a PRIMARY KEY definition.

Why it happens
  1. 1PRIMARY KEY includes a column without a NOT NULL constraint
  2. 2ORM generates a PRIMARY KEY on an optional (nullable) column
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (id INT, PRIMARY KEY (id));
-- 'id' is nullable by default

expected output

ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

Fix

Add NOT NULL to the primary key column

WHEN Always — primary key columns must be NOT NULL.

Add NOT NULL to the primary key column
CREATE TABLE t (id INT NOT NULL, PRIMARY KEY (id));
-- or more concisely:
CREATE TABLE t (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

Why this works

Primary keys uniquely identify rows; NULL cannot satisfy uniqueness constraints (NULL != NULL in SQL), so NOT NULL is required.

What not to do

✕ Use UNIQUE instead of PRIMARY KEY to avoid the NOT NULL requirement

UNIQUE allows multiple NULLs; if you need a true primary key, use NOT NULL. If NULLs are valid, reconsider whether this column should be the primary key.

Sources
Official documentation ↗

MySQL 8.0 — 1171 ER_NULL_COLUMN_IN_INDEX

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors