📋

Description

Error 3026, 'Duplicate entry', indicates that an attempt was made to insert or update a record with a value that already exists in a column defined as unique (e.g., PRIMARY KEY or UNIQUE index). This prevents data integrity issues by ensuring no two rows share the same value for that specific column.

💬

Error Message

Duplicate entry '%s'.

🔍

Known Causes

4 known causes

⚠️

Accidental Duplicate Data Entry

A user or application attempted to insert data that unintentionally duplicates an existing unique value in the database.

⚠️

Application Logic Error

The application's code logic does not adequately check for existing unique values before attempting an insert or update operation, leading to constraint violation.

⚠️

Concurrent Write Operations

Multiple simultaneous transactions attempt to insert the same unique value, with one succeeding just before another, leading to a duplicate error for the second transaction.

⚠️

Faulty Data Import/Migration

During a data import or migration process, duplicate values were present in the source data or created due to incorrect mapping, violating unique constraints.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate Records medium

Locate and delete the offending duplicate entry from the table.

1

Identify the table and the column(s) that are causing the duplicate entry. The error message '%s' will usually provide the value that is duplicated. You can use a `SELECT` query to find the existing records.

SELECT COUNT(*) FROM your_table WHERE your_column = 'duplicated_value';
-- Replace 'your_table' and 'your_column' with your actual table and column names.
-- Replace 'duplicated_value' with the value from the error message.

2

If you confirm there are duplicates, you need to decide which one to keep. If you have a primary key or another unique identifier, use that to distinguish them. Then, delete the duplicate record(s).

DELETE FROM your_table WHERE your_column = 'duplicated_value' AND primary_key_column != (SELECT MIN(primary_key_column) FROM your_table WHERE your_column = 'duplicated_value');
-- This example assumes you want to keep the record with the lowest primary_key_column.
-- Adjust the `WHERE` clause based on your criteria for keeping a record.

3

After removing the duplicate, re-attempt the original operation that caused the error.

2. Temporarily Disable Unique Constraints medium

Bypass the constraint violation by temporarily disabling unique indexes.

1

Identify the unique index or primary key constraint that is being violated. You can find this information in your `CREATE TABLE` statement or by querying `SHOW INDEX FROM your_table;`.

SHOW INDEX FROM your_table;
-- Look for 'UNIQUE' or 'PRIMARY' in the 'Non_unique' column.

2

Drop the unique index or constraint. **Be cautious with this step, as it removes the guarantee of uniqueness until it's re-added.**

ALTER TABLE your_table DROP INDEX index_name;
-- Replace 'index_name' with the name of the unique index.

3

Perform the operation that was failing. Since the constraint is temporarily removed, the duplicate entry might be allowed.

4

Re-create the unique index or constraint. **It is crucial to re-add this to maintain data integrity.** You might need to clean up duplicates before re-adding.

ALTER TABLE your_table ADD UNIQUE INDEX index_name (column1, column2, ...);
-- Replace 'index_name' and 'column1, column2, ...' with the original index details.

3. Modify Application Logic to Prevent Duplicates advanced

Adjust your application's code to check for existing records before inserting.

1

In your application's code (e.g., backend language like Python, Java, PHP, Node.js), before executing an `INSERT` statement, perform a `SELECT` query to check if a record with the same unique identifier already exists.

// Example in Python using a hypothetical ORM
existing_record = session.query(YourModel).filter_by(your_column='value_to_insert').first()
if not existing_record:
    # Proceed with insertion
    new_record = YourModel(your_column='value_to_insert')
    session.add(new_record)
    session.commit()

2

If a record already exists, either skip the insertion, update the existing record, or return an appropriate error message to the user. This prevents the MySQL server from encountering the duplicate entry error.

🔗

Related Errors

5 related errors

MySQL Error 3026: Duplicate List Entry