EC
1786
MySQLERRORNotableDDLHIGH confidence

INPLACE ALTER not supported — table has no primary key

Production Risk

Medium — performance and DDL limitations without PK.

What this means

The in-place ALTER TABLE operation is not supported because the InnoDB table lacks a primary key, which is required for certain INPLACE operations.

Why it happens
  1. 1InnoDB table without a PRIMARY KEY undergoing an ALTER TABLE ALGORITHM=INPLACE operation.
How to reproduce
trigger — this will error
trigger — this will error
ALTER TABLE no_pk_table ALGORITHM=INPLACE, ADD COLUMN new_col INT;

expected output

ERROR 1786 (0A000): ALGORITHM=INPLACE is not supported. Reason: Table has no primary key.

Fix

Add a primary key to the table, or use ALGORITHM=COPY

Add a primary key to the table, or use ALGORITHM=COPY
ALTER TABLE no_pk_table ALGORITHM=COPY, ADD COLUMN new_col INT;
-- Or add a PK:
ALTER TABLE no_pk_table ADD PRIMARY KEY (id), ADD COLUMN new_col INT;

Why this works

InnoDB INPLACE operations require a PK to track row identity.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1786 ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_NOPK

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

← All MySQL errors