How to Fix MySQL Error 1171 (ER_PRIMARY_CANT_HAVE_NULL)

Published
Length
3 min read

Luis Felipe Dias

All posts

You get mysql error 1171 when a primary key column allows null values. Fix it by setting key columns to not null or using a unique key. The database returns symbol ER_PRIMARY_CANT_HAVE_NULL with SQLSTATE 42000 during table changes. The message states that all parts of a primary key must be not null. If you need null in a key, use a unique key instead. Why mysql error 1171 happens in database changes A primary key identifies every single row in a database table. Because of this rule, no part

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You get mysql error 1171 when a primary key column allows null values. Fix it by setting key columns to not null or using a unique key.

The database returns symbol ER_PRIMARY_CANT_HAVE_NULL with SQLSTATE 42000 during table changes. The message states that all parts of a primary key must be not null. If you need null in a key, use a unique key instead.

Why mysql error 1171 happens in database changes

A primary key identifies every single row in a database table. Because of this rule, no part of the key can ever be empty. MySQL checks this rule when you create or alter a table.

In the past, some database modes changed columns to not null on their own. Strict database modes now block the change and return mysql error 1171 instead. This happens when a script sets a column as null and primary key together.

It also happens with multi-column keys. If just one column in the group allows null values, the whole change fails. Automated migration tools often trigger this when they modify column types without full definitions.

How to find the faulty column in your schema

Start by reading your migration script or table change statement. Look at all column listed inside the primary key definition. Check if any of those columns have an explicit null setting.

If you are changing an existing table, check the current column rules first. Read the table schema to see if the target column allows nulls. Also check if the table already holds empty values in that column.

Do not grant broad admin rights to developers just to check table schemas. Use read-only roles to inspect column metadata and count empty rows in production. If the column holds sensitive data like emails or phone numbers, use data masking. Masking rewrites matched fields in results while you check for empty values.

MySQL Access Governance + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

How to fix the primary key failure in production

You have two ways to fix this error based on your data needs. First, decide if every row must have a real value in this column. If yes, update any existing empty rows with valid values first.

Then change the column definition so it requires a value before adding the key. In your table creation script, declare the column as not null right away. Run the schema change through an approved deployment pipeline or peer review workflow.

Second, you might actually need to allow empty values in that column. For example, an optional national ID or card number column might be blank. In that case, do not use a primary key on that column.

Use a unique key for the optional column instead. A unique key stops duplicate values while still allowing empty fields in MySQL. Add a separate auto-increment column to serve as your true primary key.

Never turn off strict database checks to force a bad migration through. Disabling safety rules can cause silent data changes and break your app later. Keep your database roles scoped to the minimum permissions needed for the fix.

Check MySQL queries and results on the wire

MySQL grants control who logs in. They do not check single queries or mask sensitive columns once a session starts. With hoop.dev, you run an open-source proxy, a sidecar for your MySQL, inline in the MySQL wire protocol. On every query an AI agent or human runs against production MySQL, RDS, or Aurora, hoop.dev decides to pass, mask, block, or hold it for review.

Multi-statement batches and prepared queries are split and checked one by one by hoop.dev guardrails, which refuse banned queries with a native MySQL error or hold risky ones for approval. On the response path, hoop.dev data masking rewrites string values in returned rows in memory, and every decision is logged. See the MIT-licensed project on GitHub.

Frequently asked questions

What does symbol ER_PRIMARY_CANT_HAVE_NULL mean?

ER_PRIMARY_CANT_HAVE_NULL is the official MySQL symbol for error code 1171 and SQLSTATE 42000. It means a table statement tried to include a nullable column in a primary key.

Can a composite primary key have one nullable column?

No, every single column in a composite primary key must forbid null values. If even one part allows nulls, the database rejects the command with error 1171.

When should I use a unique key instead of a primary key?

Use a unique key when values must not repeat, but some rows lack data. MySQL allows multiple null values inside a unique key column without raising an error.

The query runs. The emails stay hidden.