📋

Description

MySQL Error 1068 indicates that you are attempting to define more than one primary key constraint for a single table. A fundamental rule in relational databases is that each table can have only one primary key, which uniquely identifies each row.

💬

Error Message

Multiple primary key defined

🔍

Known Causes

4 known causes

⚠️

Duplicate PRIMARY KEY Clauses

Explicitly specifying the `PRIMARY KEY` keyword multiple times within a single `CREATE TABLE` or `ALTER TABLE` statement for different columns or sets of columns.

⚠️

Implicit Primary Key Conflict

Defining a column as `UNIQUE NOT NULL` while also explicitly defining another column or set of columns as the `PRIMARY KEY`. MySQL treats the first `UNIQUE NOT NULL` index as a primary key if no explicit one exists, leading to a conflict if another is then defined.

⚠️

Misconfigured Composite Key

Incorrectly attempting to define individual columns as `PRIMARY KEY` when the intention was to create a single composite primary key, which should be declared once for multiple columns.

⚠️

Adding to Existing Primary Key

Trying to add a new primary key to a table that already has one defined, without first dropping the existing primary key constraint.

🛠️

Solutions

4 solutions available

1. Remove Duplicate PRIMARY KEY easy

A table can only have one primary key

1

Check your CREATE TABLE statement

-- Wrong:
CREATE TABLE users (
  id INT PRIMARY KEY,
  user_id INT PRIMARY KEY  -- Error!
);

-- Right - single primary key:
CREATE TABLE users (
  id INT PRIMARY KEY,
  user_id INT UNIQUE NOT NULL
);

2

For composite primary key, use constraint syntax

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

2. Use Composite Primary Key easy

Combine columns into single primary key

1

Define composite key at table level

CREATE TABLE enrollments (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrolled_at DATETIME,
  PRIMARY KEY (student_id, course_id)
);

3. Use UNIQUE Instead of PRIMARY KEY easy

Other unique columns should use UNIQUE constraint

1

Replace extra primary key with unique

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  username VARCHAR(50) UNIQUE NOT NULL
);

4. Fix ALTER TABLE Adding Primary Key medium

Cannot add primary key if one exists

1

Check existing primary key

SHOW INDEX FROM your_table WHERE Key_name = 'PRIMARY';

2

Drop existing before adding new

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

3

Or modify existing primary key

ALTER TABLE your_table DROP PRIMARY KEY, ADD PRIMARY KEY (col1, col2);

🔗

Related Errors

5 related errors

MySQL Error 1068: Multiple Primary Key Defined