MySQL says Multiple primary key defined in CREATE or ALTER TABLE
ERROR 1068 (42000): Multiple primary key definedShort answer
A MySQL table can have only one PRIMARY KEY. Error 1068 means your statement declares a second one: PRIMARY KEY on two columns, a column-level and a table-level key together, or an ADD PRIMARY KEY on a table that already has one. For a composite key, use one PRIMARY KEY (a, b) clause.
Fix it fast
1Find out which cause you have
Read how the table is defined now, or how your CREATE TABLE declares keys. Count every PRIMARY KEY you can find.
SHOW CREATE TABLE orders\G2Match what you see, then jump to its fix
- 1
Two column lines each end in PRIMARY KEYPRIMARY KEY is written on two columns, meaning a composite keyFix - 2
An id column has PRIMARY KEY or AUTO_INCREMENT PRIMARY KEY and a PRIMARY KEY (id) line followsA column-level PRIMARY KEY and a table-level PRIMARY KEY both existFix - 3
The failing statement is ALTER TABLE ... ADD PRIMARY KEYALTER TABLE adds a primary key to a table that already has oneFix
- 1
What does the error look like?
ERROR 1068 (42000): Multiple primary key defined
Cause 1 of 3
PRIMARY KEY is written on two columns, meaning a composite key
1Check
Per the manual, the PRIMARY KEY attribute in a column definition only marks that one column. Two such columns are two primary keys.
CREATE TABLE order_items (
order_id INT NOT NULL PRIMARY KEY,
product_id INT NOT NULL PRIMARY KEY
);2Fix
Remove the attribute from both columns and add one separate PRIMARY KEY clause that lists both.
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
PRIMARY KEY (order_id, product_id)
);3Verify
The table is created and shows one key with two columns.
SHOW CREATE TABLE order_items\GConfirmed by dev.mysql.com
Cause 2 of 3
A column-level PRIMARY KEY and a table-level PRIMARY KEY both exist
1Check
A table can have only one PRIMARY KEY. Declaring the same key twice, once inline and once as a clause, counts as two. This often happens after pasting a dump or a generated schema over a hand-written one.
grep -n "PRIMARY KEY" schema.sql2Fix
Keep one. An AUTO_INCREMENT column must be indexed, and the primary key is the usual way.
CREATE TABLE customers (
id INT NOT NULL AUTO_INCREMENT,
email VARCHAR(190) NOT NULL,
PRIMARY KEY (id)
);3Verify
The CREATE TABLE runs without error.
SHOW CREATE TABLE customers\GConfirmed by dev.mysql.com
Cause 3 of 3
ALTER TABLE adds a primary key to a table that already has one
1Check
Check the current keys first. If one exists, it must be dropped before or together with the new one.
SHOW KEYS FROM orders WHERE Key_name = 'PRIMARY';2Fix
Drop and add in one statement; the manual allows several ADD and DROP clauses in a single ALTER TABLE. If the old key sits on an AUTO_INCREMENT column, redefine that column in the same statement so it stays indexed.
ALTER TABLE orders
DROP PRIMARY KEY,
ADD PRIMARY KEY (order_no, region);3Verify
SHOW KEYS now lists only the new key.
SHOW KEYS FROM orders WHERE Key_name = 'PRIMARY';Confirmed by dev.mysql.com
How do I stop it coming back?
- Write composite keys as one separate PRIMARY KEY (a, b) clause.
- Run generated or pasted schemas in a scratch database first.
- Check SHOW KEYS before every ALTER that touches keys.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Duplicate entry for key 'PRIMARY' when inserting a row
Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'
Error 1062 says a row with that exact key value is already there.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix