📋

Description

MySQL Error 1291 indicates that an operation attempted to insert or update a value into a column that already contains that specific value, violating a uniqueness constraint. This typically occurs when a `UNIQUE` index or `PRIMARY KEY` constraint is defined on the column, preventing duplicate entries.

💬

Error Message

Column '%s' has duplicated value '%s' in %s

🔍

Known Causes

4 known causes

⚠️

Primary Key Constraint Violation

An insert or update operation tried to add a record with a value that duplicates an existing `PRIMARY KEY` value in the specified column.

⚠️

Unique Index Constraint Violation

An attempt was made to insert or update a value into a column that is covered by a `UNIQUE` index, and that value already exists in another row.

⚠️

Duplicate Data During Import

This error can occur during bulk data imports or migrations when the source data contains duplicate values for a column with a unique constraint in the target table.

⚠️

Application Logic Error

The application code is attempting to insert or update data without first checking for existing unique values, leading to a constraint violation.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate Rows medium

Find and delete rows that contain the duplicate value in the specified column.

1

Identify the table and column causing the duplicate value. The error message usually provides this information (e.g., `Column 'username' has duplicated value 'johndoe' in 'users'`).

2

Run a query to find rows that share the duplicate value. Replace `your_table`, `your_column`, and `'duplicate_value'` with the actual names from the error message.

SELECT * FROM your_table WHERE your_column = 'duplicate_value';

-- To find the specific rows causing the duplicate (e.g., if it's a unique constraint violation):
SELECT your_column, COUNT(*) FROM your_table GROUP BY your_column HAVING COUNT(*) > 1;

3

Carefully examine the identified rows. Decide which row(s) to keep and which to delete. If the duplicate is due to an unintentional insert, you might want to delete the newly inserted row.

4

Delete the duplicate rows. Be extremely cautious with DELETE statements. It's highly recommended to back up your data before proceeding.

DELETE FROM your_table WHERE primary_key_column IN (
    SELECT pk FROM (
        SELECT pk AS pk, ROW_NUMBER() OVER (PARTITION BY your_column ORDER BY primary_key_column) as rn
        FROM your_table
        WHERE your_column = 'duplicate_value'
    ) AS temp WHERE rn > 1;
);

5

If the error occurred during an `INSERT` or `UPDATE` operation, retry the operation after removing the duplicates.

2. Modify or Remove the Unique Constraint medium

If the column is not intended to be unique, remove the unique constraint.

1

Identify the table and column that has the unique constraint causing the error. You can find this by examining your `CREATE TABLE` statement or by querying the schema.

SHOW CREATE TABLE your_table;

2

If the column is genuinely not meant to have unique values, you can drop the unique constraint. Replace `your_table` and `constraint_name` (often the column name itself for single-column unique constraints) with your table and constraint names.

ALTER TABLE your_table DROP INDEX constraint_name;

3

If the constraint is a `PRIMARY KEY` and the duplicate is an accidental violation, you will need to remove the duplicate data first (refer to Solution 1) before you can proceed. If the `PRIMARY KEY` is truly problematic, consider a different primary key strategy.

4

After dropping the constraint, re-run the operation that caused the error.

3. Update the Conflicting Row easy

Modify the existing row to have a unique value if the duplicate is an unintentional update.

1

Identify the table and column with the duplicate value, along with the specific duplicate value and the table name from the error message.

2

Find the row(s) that already contain the `'duplicate_value'` in `your_column`. You might need to use a primary key or another unique identifier to pinpoint the exact row you want to modify.

SELECT * FROM your_table WHERE your_column = 'duplicate_value';

3

Update the conflicting row to a different, unique value. Replace `your_table`, `your_column`, `'duplicate_value'`, and `'new_unique_value'` with your specific details. Use a primary key or another identifier to target the correct row.

UPDATE your_table SET your_column = 'new_unique_value' WHERE primary_key_column = specific_row_id;

4

Retry the `INSERT` or `UPDATE` operation that initially failed.

🔗

Related Errors

5 related errors

MySQL Error 1291: Duplicated Value in Column