Multiple primary key defined
Error 1068 (SQLSTATE 42000) is raised when a CREATE TABLE or ALTER TABLE statement defines more than one PRIMARY KEY. A table can have only one primary key, although that key may be composite (spanning multiple columns).
- 1CREATE TABLE defines PRIMARY KEY twice — once inline on a column and once as a table-level constraint
- 2ALTER TABLE ADD PRIMARY KEY when a primary key already exists
- 3ORM-generated migration adds a PRIMARY KEY without first dropping the existing one
Defining two PRIMARY KEY constraints on the same table.
CREATE TABLE orders ( id INT PRIMARY KEY, order_num INT, PRIMARY KEY (order_num) -- second PRIMARY KEY );
expected output
ERROR 1068 (42000): Multiple primary key defined
Fix 1
Remove the duplicate PRIMARY KEY declaration
WHEN Always — choose one form: inline or table-level.
-- Preferred: single composite primary key at table level CREATE TABLE orders ( id INT NOT NULL, order_num INT NOT NULL, PRIMARY KEY (id) -- only one PRIMARY KEY );
Why this works
A table has exactly one primary key. If you need to uniquely identify rows by multiple columns, define a composite primary key: PRIMARY KEY (col1, col2).
Fix 2
Drop existing primary key before adding a new one in ALTER TABLE
WHEN When changing the primary key on an existing table.
ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY (id);
Why this works
DROP PRIMARY KEY must precede ADD PRIMARY KEY in the same ALTER TABLE statement.
✕ Add UNIQUE indexes as a workaround instead of fixing the PK
UNIQUE indexes have different semantics (allow NULL) and do not set the clustered index on InnoDB.
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev