How to Fix MySQL Error 1171: All Parts of PRIMARY KEY Must Be NOT NULL

ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead is raised when a CREATE TABLE or ALTER TABLE statement attempts to define a PRIMARY KEY on a column that allows NULL values. The error symbol is ER_PRIMARY_CANT_HAVE_NULL.

Impact

This error is a DDL-time failure — the CREATE TABLE or ALTER TABLE statement is rejected entirely, and no schema change is made. It cannot occur during INSERT or UPDATE operations.

Developers most commonly encounter this when writing migration scripts, scaffolding ORM models, or copying table definitions between databases. ORMs such as Hibernate, SQLAlchemy, and ActiveRecord typically enforce NOT NULL on primary key columns automatically, so this error usually surfaces when working with raw SQL migrations, legacy schema imports, or manual ALTER TABLE statements. CI pipelines that run schema migrations against a fresh database will fail at the affected migration with no rows affected.

Common Causes

Explicit NULL declaration on a PRIMARY KEY column. A column in the PRIMARY KEY clause is declared with NULL (or DEFAULT NULL), which directly contradicts the primary key constraint.
Missing NOT NULL on a non-auto-increment composite key column. In a composite primary key, every participating column must be NOT NULL. Forgetting the constraint on even one column raises the error.
**Importing or copying a CREATE TABLE statement where the column definition was written for a UNIQUE index but later changed to PRIMARY KEY without updating the nullability.
Altering an existing nullable column to become part of a primary key using ALTER TABLE ... ADD PRIMARY KEY when the column still allows NULL.
ORM code generation or code scaffolding that emits an explicit NULL annotation for columns that are then also marked as the primary key, resulting in contradictory DDL.

Troubleshooting and Resolution Steps

Identify the offending column. Look at the CREATE TABLE or ALTER TABLE statement that triggered the error. The error message does not name the specific column, so examine each column in the PRIMARY KEY clause.
```
-- Example that triggers the error
CREATE TABLE orders (
    order_id INT NULL,
    PRIMARY KEY (order_id)
);
-- ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL
```

Add NOT NULL to the column definition. This is the standard fix for new table creation:

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    PRIMARY KEY (order_id)
);

Fix a composite primary key by ensuring every column in the key is NOT NULL:

-- Incorrect — user_id is nullable
CREATE TABLE user_roles (
    user_id INT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id)
);

-- Correct
CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id)
);

Fix an existing table where you want to add a primary key to a currently nullable column. First modify the column, then add the constraint:

-- Check current column definition
SHOW COLUMNS FROM orders LIKE 'order_id';

-- Step 1: make the column NOT NULL (set a default or update NULLs first)
UPDATE orders SET order_id = 0 WHERE order_id IS NULL;
ALTER TABLE orders MODIFY order_id INT NOT NULL;

-- Step 2: add the primary key
ALTER TABLE orders ADD PRIMARY KEY (order_id);

If NULLs are intentional, and you need uniqueness without the NOT NULL requirement, use a UNIQUE index instead (as suggested in the error message itself):
```
CREATE TABLE sessions (
    session_token VARCHAR(64) NULL,
    UNIQUE KEY (session_token)
);
```
Keep in mind that MySQL allows multiple NULL values in a UNIQUE index, so two rows can both have session_token = NULL without violating uniqueness.

Inspect an existing table that you are trying to alter:

-- Check nullability of all columns
SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'your_table';

Additional Information

The SQLSTATE code 42000 represents a syntax or access rule violation in the SQL standard, which is correct here — the DDL violates relational integrity rules.
MySQL implicitly adds NOT NULL to AUTO_INCREMENT columns, so INT AUTO_INCREMENT PRIMARY KEY without an explicit NOT NULL keyword will not trigger this error. The implicit constraint is applied automatically.
In contrast to error 1171, error 1364 (Field 'x' doesn't have a default value) is a DML-time error raised during INSERT. They are unrelated.
Error 1075 (Incorrect table definition; there can be only one auto column and it must be defined as a key) is a related DDL error you may encounter when misconfiguring AUTO_INCREMENT columns.
MariaDB has the same error code and behavior as MySQL for this constraint.
Some schema diff tools (e.g., gh-ost, pt-online-schema-change) will propagate this error when replaying DDL. The fix must be applied to the source migration script.

Frequently Asked Questions

Why does MySQL allow NULL in a UNIQUE index but not in a PRIMARY KEY? The SQL standard treats NULL as "unknown", meaning two NULL values are not considered equal. A UNIQUE index leverages this: multiple NULL entries are permitted. A PRIMARY KEY, however, must uniquely identify every row, which requires a known, non-null value. MySQL enforces this distinction strictly.

Does adding AUTO_INCREMENT automatically satisfy the NOT NULL requirement? Yes. MySQL implicitly treats AUTO_INCREMENT columns as NOT NULL. You can omit the explicit NOT NULL keyword when using AUTO_INCREMENT, and MySQL will not raise error 1171. Explicitly adding NOT NULL is still good practice for clarity.

Can I use a nullable column as part of a composite primary key if at least one column is NOT NULL? No. Every column in a composite PRIMARY KEY must be NOT NULL. MySQL does not allow any nullable column in the primary key, regardless of the other columns in the composite key.

My ORM generated the schema and it raises this error. What should I check? Check your model definition for a column annotated both as nullable=True (or equivalent) and as the primary key. The ORM may have emitted NULL or omitted NOT NULL in the DDL. Either update the model annotation to mark the column as non-nullable, or edit the generated migration before running it.