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.
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.
Before making changes, examine the existing table definition to understand what primary keys or constraints already exist:
SHOW CREATE TABLE your_table_name;Or use DESCRIBE to see column details:
DESCRIBE your_table_name;Look for any columns marked with "PRI" in the Key column—these are part of the existing primary key.
A composite primary key is a single primary key made up of multiple columns. This is allowed:
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):
CREATE TABLE orders (
id INT PRIMARY KEY, -- First primary key
code INT PRIMARY KEY -- Second primary key - NOT ALLOWED!
);If your CREATE TABLE statement has multiple PRIMARY KEY definitions, remove the duplicates and keep only one. For example, change:
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(255) PRIMARY KEY, -- Remove this!
name VARCHAR(100)
);To:
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:
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE, -- Use UNIQUE constraint instead
name VARCHAR(100)
);If you're adding a primary key to an existing table that already has one, drop the current primary key first:
-- 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:
ALTER TABLE your_table DROP PRIMARY KEY;
ALTER TABLE your_table ADD PRIMARY KEY (column1, column2);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:
-- 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):
[mysqld]
sql_generate_invisible_primary_key = OFFOnce you've updated the DDL, apply the changes:
-- 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.
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.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions