📋

Description

This error indicates that an attempt was made to insert or update a row with a value that already exists in a column defined as `UNIQUE` or `PRIMARY KEY`. It occurs when a new record or an updated record introduces a non-unique value into a field that requires uniqueness, such as an ID or a specific identifier.

💬

Error Message

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

🔍

Known Causes

4 known causes

⚠️

Primary Key Violation

An `INSERT` or `UPDATE` statement tried to add a record with a value that already exists in the table's `PRIMARY KEY` column, which must be unique.

⚠️

Unique Constraint Violation

A value being inserted or updated into a column violates a `UNIQUE` constraint or `UNIQUE` index specifically defined on that column.

⚠️

Data Import Conflict

During a data import or migration process, the source data contains duplicate values for columns that are defined as unique in the target MariaDB table.

⚠️

Application Logic Error

The application or script generating data is not properly ensuring uniqueness for fields that require it, leading to repeated attempts to insert identical values.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate Rows medium

Find and delete rows containing the duplicate value that violates the unique constraint.

1

Identify the table and column causing the duplicate error. The error message provides this information.

SELECT COUNT(*) FROM your_table WHERE your_column = 'duplicate_value';
SELECT * FROM your_table WHERE your_column = 'duplicate_value';

2

If the duplicate value is not intended, determine which row to keep and which to delete. Often, you'll keep the row with the earliest timestamp or specific identifier.

SELECT * FROM your_table WHERE your_column = 'duplicate_value' ORDER BY your_primary_key ASC;

3

Delete the duplicate row(s). Replace `your_primary_key` with the actual primary key column of your table.

DELETE FROM your_table WHERE your_primary_key = (SELECT your_primary_key FROM your_table WHERE your_column = 'duplicate_value' ORDER BY your_primary_key DESC LIMIT 1);

4

Verify that the duplicate has been removed and the constraint is no longer violated.

SELECT COUNT(*) FROM your_table WHERE your_column = 'duplicate_value';

2. Temporarily Disable and Re-enable Unique Constraint medium

Disable the unique constraint, fix the data, and then re-enable it.

1

Identify the table and the unique constraint causing the error. You can find this by running `SHOW CREATE TABLE your_table;` and looking for `UNIQUE KEY` or `PRIMARY KEY` definitions.

SHOW CREATE TABLE your_table;

2

Temporarily disable the unique constraint. Replace `constraint_name` with the actual name of your unique constraint.

ALTER TABLE your_table DROP INDEX constraint_name;

3

Now, you can insert or update the data that was previously failing. If you know the exact duplicate value, you can address it as in the previous solution, or if it's a new insert, you might need to adjust the incoming data.

-- Example: If you were trying to insert a duplicate:
INSERT INTO your_table (your_column, other_column) VALUES ('duplicate_value', 'some_data');
-- Or if you need to update an existing row to remove a duplicate:
UPDATE your_table SET your_column = 'new_unique_value' WHERE your_primary_key = 'id_of_row_to_update';

4

Re-enable the unique constraint. You might need to recreate it with the same definition.

ALTER TABLE your_table ADD UNIQUE INDEX constraint_name (your_column);

3. Modify or Remove the Unique Constraint easy

If the uniqueness is no longer required, permanently remove or alter the constraint.

1

Identify the table and the unique constraint. Use `SHOW CREATE TABLE your_table;` to find the constraint name.

SHOW CREATE TABLE your_table;

2

If the column should no longer be unique, drop the unique constraint. Replace `constraint_name` with the actual name of your unique constraint.

ALTER TABLE your_table DROP INDEX constraint_name;

3

If you need to allow duplicates but still want some form of uniqueness, consider altering the constraint to be part of a composite unique index or changing the data type if applicable.

-- Example: Creating a composite unique index if other columns also define uniqueness
ALTER TABLE your_table ADD UNIQUE INDEX composite_unique_index (your_column, another_column);

🔗

Related Errors

5 related errors

MariaDB Error 1291: Duplicate Value in Column