1068ERRORTier 2 — Caution✅ HIGH confidence

Multiple primary key defined

Category: DDLVersions: All MariaDB / MySQL versions

What this means

Error 1068 (SQLSTATE 42000) is raised when a CREATE TABLE or ALTER TABLE statement defines more than one PRIMARY KEY. A table can have only one primary key, although that key may be composite (spanning multiple columns).

Why it happens

1CREATE TABLE defines PRIMARY KEY twice — once inline on a column and once as a table-level constraint
2ALTER TABLE ADD PRIMARY KEY when a primary key already exists
3ORM-generated migration adds a PRIMARY KEY without first dropping the existing one

How to reproduce

Defining two PRIMARY KEY constraints on the same table.

trigger — this will ERROR

CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_num INT,
  PRIMARY KEY (order_num)  -- second PRIMARY KEY
);

ERROR 1068 (42000): Multiple primary key defined

Fix 1: Remove the duplicate PRIMARY KEY declaration

Always — choose one form: inline or table-level.

fix

-- Preferred: single composite primary key at table level
CREATE TABLE orders (
  id INT NOT NULL,
  order_num INT NOT NULL,
  PRIMARY KEY (id)  -- only one PRIMARY KEY
);

Why this works

A table has exactly one primary key. If you need to uniquely identify rows by multiple columns, define a composite primary key: PRIMARY KEY (col1, col2).

Fix 2: Drop existing primary key before adding a new one in ALTER TABLE

When changing the primary key on an existing table.

fix

ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY (id);

Why this works

DROP PRIMARY KEY must precede ADD PRIMARY KEY in the same ALTER TABLE statement.

What not to do

✗

Add UNIQUE indexes as a workaround instead of fixing the PK

Why it's wrong: UNIQUE indexes have different semantics (allow NULL) and do not set the clustered index on InnoDB.

Sources

📚 Official docs: https://mariadb.com/kb/en/create-table/#primary-key

🔧 Source ref: MariaDB Server error code 1068 / ER_MULTIPLE_PRI_KEY

📖 Further reading: MariaDB Primary Keys

Confidence assessment

✅ HIGH confidence

Stable.