<p>Attempting to drop a PRIMARY KEY without defining a new one triggers MySQL error 1853.</p>
<p>MySQL Error 1853 (ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_NOPK) happens when an ALTER TABLE statement removes the primary key but does not add a replacement key in the same command. Add an ADD PRIMARY KEY clause or create a new key first, then drop the old one, to resolve the error.</p>
Dropping a primary key is not allowed without also adding
Error 1853 appears when an ALTER TABLE statement tries to drop the existing primary key without simultaneously defining another one. MySQL blocks this action to protect row uniqueness and index integrity.
The server returns SQLSTATE HY000 and the message "Dropping a primary key is not allowed without also adding". The rule was enforced starting with MySQL 5.7.1.
The error is triggered by ALTER TABLE commands that include DROP PRIMARY KEY alone or combined with other changes but without an ADD PRIMARY KEY clause. MySQL requires every InnoDB table to keep exactly one primary key.
It can also occur when a developer mistakenly thinks a unique key can replace a primary key automatically. MySQL does not convert unique indexes into primary keys unless explicitly declared.
Include ADD PRIMARY KEY(column_list) in the same ALTER TABLE statement that drops the current key. Alternatively, create a new primary key first, then issue a second statement to drop the old one.
Ensure the replacement key covers non-NULL columns and is truly unique across all rows. Test on a staging database before applying to production.
Renaming a primary key column often requires rebuilding the key. Use ADD PRIMARY KEY (new_column) after altering the column name.
Switching from an integer surrogate key to a composite business key requires creating the composite primary key first, followed by dropping the surrogate key.
Plan schema migrations with explicit primary key definitions. Wrap related alterations in a single transactional ALTER TABLE where possible to minimize downtime.
Use tools like pt-online-schema-change or Galaxy's migration helpers to rehearse alterations and maintain continuous availability.
Error 1068 (Multiple primary key defined) surfaces when you attempt to add a second primary key without dropping the first. Resolve by ensuring only one primary key exists.
Error 1075 (Incorrect table definition) occurs when a primary key column allows NULLs. Declare columns as NOT NULL before adding them to a primary key.
The ALTER TABLE statement contains DROP PRIMARY KEY but no ADD PRIMARY KEY clause.
Developers assume a UNIQUE constraint automatically becomes the primary key, which is false.
Generated scripts reorder statements, causing DROP PRIMARY KEY to execute before ADD PRIMARY KEY.
Occurs when two PRIMARY KEY clauses exist in one table.
Raised when a primary key column is nullable or of an unsupported type.
Appears when dropping a primary key that foreign keys depend on.
InnoDB technically allows it but is discouraged. MySQL enforces a primary key for certain operations and performance optimizations.
A UNIQUE index enforces uniqueness but does not replace the need for a primary key. Only a primary key guarantees clustered index behavior in InnoDB.
Create the new primary key in the same ALTER TABLE statement or transaction, verify uniqueness, then drop the old key.
Galaxy's SQL linting flags ALTER TABLE commands that drop a primary key without adding a new one, reducing runtime errors.
.avif)
.avif){kind=logo}
