EC
1068
MariaDBERRORNotableDDLHIGH confidence

Multiple primary key defined

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
  1. 1CREATE TABLE defines PRIMARY KEY twice — once inline on a column and once as a table-level constraint
  2. 2ALTER TABLE ADD PRIMARY KEY when a primary key already exists
  3. 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
trigger — this will error
CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_num INT,
  PRIMARY KEY (order_num)  -- second PRIMARY KEY
);

expected output

ERROR 1068 (42000): Multiple primary key defined

Fix 1

Remove the duplicate PRIMARY KEY declaration

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

Remove the duplicate PRIMARY KEY declaration
-- 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 When changing the primary key on an existing table.

Drop existing primary key before adding a new one in ALTER TABLE
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

UNIQUE indexes have different semantics (allow NULL) and do not set the clustered index on InnoDB.

Sources
Official documentation ↗

MariaDB Server error code 1068 / ER_MULTIPLE_PRI_KEY

MariaDB Primary Keys ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MariaDB errors