How to fix ERROR 1068: Multiple primary key defined in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 253 views

MySQL only allows one primary key per table. This error occurs when you define multiple PRIMARY KEY constraints in a CREATE TABLE or ALTER TABLE statement. Remove duplicate primary key definitions or use a composite primary key instead.

Quick fix

Fixes ERROR 1068: Multiple primary key defined

SHOW CREATE TABLE your_table_name;
  1. Identify what the current table structure looks like
  2. Understand the difference: composite vs. multiple primary keys
  3. Fix by removing the duplicate PRIMARY KEY definition
  4. Fix ALTER TABLE by dropping the existing primary key first
mysql — error
SHOW CREATE TABLE your_table_name;
ERROR 1068: Multiple primary key defined

What this error means

MySQL enforces a strict constraint that each table can have exactly one primary key. When you attempt to define more than one primary key—either by specifying multiple PRIMARY KEY clauses in CREATE TABLE, adding a primary key to a table that already has one, or due to MySQL's invisible primary key feature (sql_generate_invisible_primary_key=ON)—MySQL rejects the operation with ERROR 1068. This error protects your schema integrity, as having multiple primary keys would violate relational database theory and create ambiguity about which column(s) uniquely identify each row.

Typical symptoms

  • CREATE TABLE statement fails when defining two columns with PRIMARY KEY
  • ALTER TABLE ADD PRIMARY KEY fails on existing table with a primary key
  • Migration tools or ORMs throw "Multiple primary key defined" errors
  • DDL imports from other databases fail with this error
  • Error appears when sql_generate_invisible_primary_key is enabled in MySQL 8.0.32+

Common causes

  1. Explicitly defining PRIMARY KEY on two or more columns instead of using a composite key
  2. Attempting to add a primary key with ALTER TABLE when the table already has one
  3. Confusing composite primary keys (multiple columns in one key) with multiple separate primary keys
  4. Using table import/migration scripts that don't account for existing primary keys
  5. MySQL's sql_generate_invisible_primary_key feature creating a hidden primary key that conflicts with an explicitly added one
  6. Copy-pasting DDL from different database systems that use different primary key syntax

How to fix "ERROR 1068: Multiple primary key defined"

1Identify what the current table structure looks like

Before making changes, examine the existing table definition to understand what primary keys or constraints already exist:

sql
SHOW CREATE TABLE your_table_name;

Or use DESCRIBE to see column details:

sql
DESCRIBE your_table_name;

Look for any columns marked with "PRI" in the Key column—these are part of the existing primary key.

2Understand the difference: composite vs. multiple primary keys

A composite primary key is a single primary key made up of multiple columns. This is allowed:

sql
CREATE TABLE orders (
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    order_id INT NOT NULL,
    PRIMARY KEY (customer_id, order_date)  -- One composite key, two columns
);

Multiple primary keys (which are not allowed):

sql
CREATE TABLE orders (
    id INT PRIMARY KEY,          -- First primary key
    code INT PRIMARY KEY         -- Second primary key - NOT ALLOWED!
);
3Fix by removing the duplicate PRIMARY KEY definition

If your CREATE TABLE statement has multiple PRIMARY KEY definitions, remove the duplicates and keep only one. For example, change:

sql
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(255) PRIMARY KEY,  -- Remove this!
    name VARCHAR(100)
);

To:

sql
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(255),  -- Make it UNIQUE if needed
    name VARCHAR(100)
);

If you need email to be unique but not the primary key:

sql
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,  -- Use UNIQUE constraint instead
    name VARCHAR(100)
);
4Fix ALTER TABLE by dropping the existing primary key first

If you're adding a primary key to an existing table that already has one, drop the current primary key first:

sql
-- First, drop the existing primary key
ALTER TABLE your_table DROP PRIMARY KEY;

-- Then add the new primary key
ALTER TABLE your_table ADD PRIMARY KEY (column_name);

For a composite primary key on multiple columns:

sql
ALTER TABLE your_table DROP PRIMARY KEY;
ALTER TABLE your_table ADD PRIMARY KEY (column1, column2);
5Handle the invisible primary key issue (MySQL 8.0.32+)

If you're using MySQL 8.0.32 or later, the sql_generate_invisible_primary_key variable may be enabled, which causes MySQL to automatically add a hidden primary key to tables without one. This can conflict when you later try to add an explicit primary key:

sql
-- Check the current setting
SHOW VARIABLES LIKE 'sql_generate_invisible_primary_key';

-- Disable it for your session
SET sql_generate_invisible_primary_key = OFF;

-- Now your CREATE TABLE or ALTER TABLE will work
CREATE TABLE users (...) ENGINE=InnoDB;

Or disable it globally in your MySQL configuration file (my.cnf or my.ini):

ini
[mysqld]
sql_generate_invisible_primary_key = OFF
6Test your fix by recreating or altering the table

Once you've updated the DDL, apply the changes:

sql
-- If creating a new table:
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100)
);

-- Verify it was created successfully:
SHOW CREATE TABLE users;

You should see only one PRIMARY KEY definition in the output. If you're using an ORM or migration tool, re-run the migration after fixing the DDL.

Advanced notes

For those working with legacy databases or migrating from other SQL systems (like SQL Server or PostgreSQL), be aware that the syntax and constraints for primary keys differ. MySQL is stricter—it absolutely forbids multiple primary keys per table. If you're importing a schema from another database, make sure the primary key definitions are adapted to MySQL's rules. Additionally, if you're using Doctrine, Laravel Migrations, or other ORMs, check that the migration files don't accidentally include duplicate PRIMARY KEY definitions. Some ORM generators may not properly detect existing primary keys when altering tables. Testing with SHOW CREATE TABLE before and after migrations is a best practice to catch these errors early.

Frequently asked questions

What does "ERROR 1068: Multiple primary key defined" mean?
MySQL enforces a strict constraint that each table can have exactly one primary key. When you attempt to define more than one primary key—either by specifying multiple PRIMARY KEY clauses in CREATE TABLE, adding a primary key to a table that already has one, or due to MySQL's invisible primary key feature (sql_generate_invisible_primary_key=ON)—MySQL rejects the operation with ERROR 1068. This error protects your schema integrity, as having multiple primary keys would violate relational database theory and create ambiguity about which column(s) uniquely identify each row.
How do I fix "ERROR 1068: Multiple primary key defined"?
MySQL only allows one primary key per table. This error occurs when you define multiple PRIMARY KEY constraints in a CREATE TABLE or ALTER TABLE statement. Remove duplicate primary key definitions or use a composite primary key instead.
What causes "ERROR 1068: Multiple primary key defined"?
Explicitly defining PRIMARY KEY on two or more columns instead of using a composite key Attempting to add a primary key with ALTER TABLE when the table already has one Confusing composite primary keys (multiple columns in one key) with multiple separate primary keys
Is the "ERROR 1068: Multiple primary key defined" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading