Error

Error Code: 1068

MariaDB Error 1068: Multiple Primary Key Defined

📦 MariaDB

📋

Description

Error 1068 in MariaDB indicates that you are attempting to define more than one primary key constraint within a single table definition. A database table is only allowed to have one primary key, which uniquely identifies each row. This error commonly arises during `CREATE TABLE` or `ALTER TABLE` statements.

💬

Error Message

Multiple primary key defined

🔍

Known Causes

4 known causes

⚠️

Duplicate PRIMARY KEY Clauses

Including the `PRIMARY KEY` keyword more than once within a single `CREATE TABLE` statement, either inline on a column or as a table constraint.

⚠️

Adding to Existing Primary Key

Executing an `ALTER TABLE ADD PRIMARY KEY` statement on a table that already has a primary key defined.

⚠️

Faulty Migration Script

Automated schema migration tools or scripts generating DDL that inadvertently attempts to define multiple primary keys.

⚠️

Copy-Paste Errors

Accidentally duplicating `PRIMARY KEY` definitions when constructing DDL statements from various sources or templates.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate PRIMARY KEY Constraints easy

Locate and drop the redundant PRIMARY KEY definition in your table schema.

Connect to your MariaDB instance using a client tool (e.g., `mariadb` command-line client, DBeaver, MySQL Workbench).

Inspect the table's `CREATE TABLE` statement or use `SHOW CREATE TABLE` to identify the conflicting PRIMARY KEY definitions.

SHOW CREATE TABLE your_table_name;

Examine the output for multiple `PRIMARY KEY` clauses. Typically, this occurs when a PRIMARY KEY is defined inline with a column and also as a separate table constraint.

Choose one of the PRIMARY KEY definitions to keep. If the PRIMARY KEY is composed of multiple columns, ensure you select the correct multi-column definition. If it's a single column, ensure it's the intended unique identifier.

Drop the duplicate PRIMARY KEY constraint using an `ALTER TABLE` statement. Replace `your_table_name` with the actual table name and `pk_constraint_name` with the name of the duplicate primary key constraint (often named PRIMARY, or derived from the column name).

ALTER TABLE your_table_name DROP PRIMARY KEY;

If the duplicate PRIMARY KEY was defined inline with a column, you might need to drop it by referencing the column name if it was implicitly created.

ALTER TABLE your_table_name DROP INDEX column_name;

Verify the PRIMARY KEY has been corrected by running `SHOW CREATE TABLE your_table_name;` again.

2. Modify Table Schema to Include a Single PRIMARY KEY medium

Recreate or alter the table to have only one correct PRIMARY KEY definition.

Connect to your MariaDB instance.

Get the current `CREATE TABLE` statement for the problematic table.

SHOW CREATE TABLE your_table_name;

Carefully review the output. Identify which column(s) should logically form the PRIMARY KEY. Ensure there is only one `PRIMARY KEY` definition in the statement.

Create a new `CREATE TABLE` statement that correctly defines the single PRIMARY KEY. You can either manually edit the existing one or generate a new one based on your schema requirements.

-- Example of a corrected CREATE TABLE statement
CREATE TABLE your_table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1 VARCHAR(255),
    column2 INT
);

If the table contains data, you will need to temporarily move the data. First, create a temporary table with the corrected schema.

CREATE TABLE your_table_name_temp LIKE your_table_name;

Copy the data from the original table to the temporary table.

INSERT INTO your_table_name_temp SELECT * FROM your_table_name;

Drop the original table.

DROP TABLE your_table_name;

Rename the temporary table to the original table name.

RENAME TABLE your_table_name_temp TO your_table_name;

Alternatively, if you prefer to alter the existing table directly (and it's safe to do so, e.g., no data loss risk), you can drop the existing PRIMARY KEY and then add the correct one.

ALTER TABLE your_table_name DROP PRIMARY KEY;
ALTER TABLE your_table_name ADD PRIMARY KEY (your_primary_key_column);

Verify the PRIMARY KEY by running `SHOW CREATE TABLE your_table_name;`.

3. Correct PRIMARY KEY Definition During Table Creation easy

Ensure only one PRIMARY KEY is defined when creating a new table.

When writing your `CREATE TABLE` statement, ensure that the `PRIMARY KEY` constraint is defined only once. It can be defined inline with a column or as a separate table constraint.

-- Correct way (inline definition)
CREATE TABLE new_table (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE
);

Or as a separate table constraint:

-- Correct way (table constraint definition)
CREATE TABLE another_table (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Avoid defining a PRIMARY KEY inline and then again as a table constraint for the same column(s).

-- INCORRECT: Will cause Error 1068
CREATE TABLE bad_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    PRIMARY KEY (id) -- Duplicate PRIMARY KEY definition
);

Ensure that any `UNIQUE` constraints are not also defined as `PRIMARY KEY` for the same column(s) if you intend for it to be a unique identifier but not the primary key.

🔗

Related Errors

5 related errors