How to Fix MySQL Error 1068: Multiple Primary Key Defined

ERROR 1068 (42000): Multiple primary key defined is raised when a CREATE TABLE or ALTER TABLE statement attempts to define more than one PRIMARY KEY constraint on the same table. MySQL (and SQL in general) allows only one primary key per table. The error symbol is ER_MULTIPLE_PRI_KEY.

Impact

The statement fails immediately and no schema change is applied. If the error occurs inside an explicit transaction or a migration script, the rest of the script may be aborted depending on how your tooling handles DDL errors.

Developers most commonly encounter this error when writing migrations by hand, copy-pasting table definitions from documentation or another schema, or when an ORM generates DDL that conflicts with manually specified constraints. Because the error is a parse-time DDL error, it surfaces at deployment time — during db:migrate, a Flyway or Liquibase migration run, or when running an init script — rather than at application runtime.

Common Causes

Two PRIMARY KEY clauses in the same CREATE TABLE statement. Defining PRIMARY KEY inline on a column and again as a table-level constraint is the most frequent cause.
ALTER TABLE ... ADD PRIMARY KEY on a table that already has a primary key. The table was created with a primary key, and a subsequent migration adds another without first dropping the existing one.
Manually combining a per-column PRIMARY KEY keyword with a separate PRIMARY KEY (col) clause. These are two different syntactic forms for the same thing, and using both together triggers the error.
Generated or scaffolded DDL with duplicate constraints. Some code generators, schema exporters, or ORM migration tools emit both the inline column keyword and a trailing constraint block, resulting in a duplicate.

Troubleshooting and Resolution Steps

Identify the duplicate definition in the failing statement. Read the CREATE TABLE or ALTER TABLE statement carefully and locate every occurrence of PRIMARY KEY.

The following statement triggers the error because id is marked PRIMARY KEY inline and a table-level PRIMARY KEY constraint is also declared:

-- Incorrect: two PRIMARY KEY definitions
CREATE TABLE orders (
  id INT NOT NULL PRIMARY KEY,
  customer_id INT NOT NULL,
  PRIMARY KEY (id)          -- duplicate — remove one
);

Fix: keep only one form.

-- Option A: inline only
CREATE TABLE orders (
  id INT NOT NULL PRIMARY KEY,
  customer_id INT NOT NULL
);

-- Option B: table-level constraint only (preferred for composite keys)
CREATE TABLE orders (
  id INT NOT NULL,
  customer_id INT NOT NULL,
  PRIMARY KEY (id)
);

Check whether a primary key already exists before running ALTER TABLE ... ADD PRIMARY KEY. Query information_schema to inspect the current indexes:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME   = 'your_table'
  AND CONSTRAINT_TYPE = 'PRIMARY KEY';

If a primary key is already present and you want to replace it, drop it first:

ALTER TABLE your_table DROP PRIMARY KEY;
ALTER TABLE your_table ADD PRIMARY KEY (new_col);

Or combine both operations in one statement:

ALTER TABLE your_table
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (new_col);

Defining a composite primary key. If your intent is a multi-column (composite) primary key, use a single PRIMARY KEY (col1, col2) table-level clause — do not mark each column individually with the PRIMARY KEY keyword:

-- Incorrect: marking each column individually
CREATE TABLE order_items (
  order_id   INT NOT NULL PRIMARY KEY,
  product_id INT NOT NULL PRIMARY KEY,  -- error
  qty        INT NOT NULL
);

-- Correct: single composite primary key
CREATE TABLE order_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  qty        INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Inspect ORM-generated or exported DDL. If your migration was generated by a tool, print the raw SQL before executing it and search for duplicate PRIMARY KEY occurrences:
```
grep -i "primary key" migration.sql
```
Remove the redundant clause or reconfigure the generator to emit only the table-level form.

Additional Information

The SQLSTATE for this error is 42000 (syntax error or access rule violation), consistent with other DDL validation errors.
This error is unrelated to SQL strict mode (STRICT_TRANS_TABLES / STRICT_ALL_TABLES); it is always enforced regardless of sql_mode.
A table may have multiple UNIQUE indexes and multiple KEY (non-unique) indexes — only PRIMARY KEY is limited to one.
In MySQL, the primary key is always stored as the clustered index in InnoDB. Replacing it requires a full table rebuild, so on large tables do this during a maintenance window or use an online DDL tool such as pt-online-schema-change or gh-ost.
Related error: ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key — occurs when AUTO_INCREMENT is used on a column that is not a key.

Frequently Asked Questions

Can a MySQL table have two primary keys? No. A table can have exactly one primary key. If you need to uniquely identify rows by a combination of columns, use a single composite primary key: PRIMARY KEY (col1, col2).

What is the difference between marking a column PRIMARY KEY inline versus using a table-level PRIMARY KEY (col) clause? They are functionally identical for single-column primary keys. The table-level form is required for composite primary keys and is generally preferred for readability. Using both forms on the same column triggers error 1068.

My ORM runs migrations automatically and I see this error in production logs — what should I check? Check whether the migration was already partially applied in a previous run. Query information_schema.TABLE_CONSTRAINTS to see the current state of the table, then review the migration file for duplicate PRIMARY KEY declarations. Many ORMs also keep a migrations history table (e.g., schema_migrations in Rails, flyway_schema_history in Flyway) that can show which steps have already run.

How do I change the primary key of an existing table without getting this error? Drop the existing primary key and add the new one, either in two separate statements or combined in one ALTER TABLE with both DROP PRIMARY KEY and ADD PRIMARY KEY (...).