Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL says Multiple primary key defined in CREATE or ALTER TABLE

ErrorERROR 1068 (42000): Multiple primary key defined

Short 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.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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\G
  2. 2Match what you see, then jump to its fix

    1. 1Two column lines each end in PRIMARY KEYPRIMARY KEY is written on two columns, meaning a composite keyFix
    2. 2An 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. 3The failing statement is ALTER TABLE ... ADD PRIMARY KEYALTER TABLE adds a primary key to a table that already has oneFix

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\G

Confirmed by dev.mysql.com

Did this fix it?

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.sql

2Fix

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\G

Confirmed by dev.mysql.com

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.