📋

Description

Error 1761 indicates a violation of a unique constraint or primary key on a child table's foreign key column(s) during an `INSERT` or `UPDATE` operation. This occurs when you attempt to insert or update a record in the child table with a foreign key value that already exists, and a unique index or primary key on that column (or a composite key involving it) in the child table prevents such duplicates.

💬

Error Message

Foreign key constraint for table '%s', record '%s' would lead to a duplicate entry in table '%s', key '%s'

🔍

Known Causes

4 known causes

⚠️

Duplicate FK Value in Unique Column

You are attempting to insert or update a foreign key value into a column (or set of columns) in the child table that has a unique index or primary key defined on it, and that value already exists for another record.

⚠️

Application Logic Error

The application or script is generating or attempting to insert foreign key values without checking for existing unique values, leading to attempts to create duplicates where not allowed.

⚠️

Data Migration Conflict

During data migration or a large bulk insert, existing unique foreign key values in the target child table are being duplicated by the incoming data.

⚠️

Misunderstood Schema Design

The table schema includes a unique constraint or primary key on the foreign key column(s) in the child table, which was not considered when designing the data insertion logic.

🛠️

Solutions

3 solutions available

1. Identify and Remove Duplicate Records in the Referenced Table medium

Locate and delete redundant rows in the parent table that violate the unique constraint.

1

Identify the tables and key involved in the error. The error message will explicitly state the referencing table, the record causing the issue, the referenced table, and the key that would be duplicated.

Example: Foreign key constraint for table 'child_table', record 'some_value' would lead to a duplicate entry in table 'parent_table', key 'parent_id'

2

Query the referenced table to find existing duplicate entries based on the unique key. This usually involves grouping by the columns that constitute the unique key and checking for counts greater than 1.

SELECT parent_id, COUNT(*) FROM parent_table GROUP BY parent_id HAVING COUNT(*) > 1;

3

Carefully examine the duplicate rows. Decide which rows are the 'correct' ones and which can be safely deleted. This might involve comparing other columns or using timestamps.

SELECT * FROM parent_table WHERE parent_id = 'the_duplicate_id_found_in_previous_step';

4

Delete the duplicate rows, ensuring you keep only one instance of each unique key value. **Backup your data before executing DELETE statements.**

DELETE FROM parent_table WHERE primary_key_column = 'id_of_duplicate_row_to_delete';

5

Re-attempt the operation that initially caused the error (e.g., INSERT or UPDATE in the referencing table).

2. Modify the Referencing Record to Use an Existing Unique Value easy

Adjust the data being inserted or updated in the child table to match an existing, valid entry in the parent table.

1

Analyze the error message to understand which record in the referencing table is causing the issue and what value it's trying to use in the referenced table.

Example: Foreign key constraint for table 'child_table', record 'some_value' would lead to a duplicate entry in table 'parent_table', key 'parent_id'

2

Examine the referenced table (e.g., `parent_table`) to confirm that the intended foreign key value already exists and is associated with a record that is not a duplicate.

SELECT * FROM parent_table WHERE parent_id = 'the_value_from_the_error_message';

3

If the foreign key value is correct and exists, but the operation is still failing, it implies that the *combination* of values in the referencing record would create a duplicate in a unique index of the referenced table. This is less common for foreign keys directly, but can happen if the foreign key is part of a composite unique index.

4

Modify the data being inserted or updated in the referencing table. Ensure that the foreign key value you are providing either points to an existing, non-duplicate record in the parent table, or that the combination of fields in your referencing record does not violate any unique constraints in the parent table.

Example: If inserting into `child_table` and `parent_id` is '123', ensure '123' exists in `parent_table` and that this insertion won't violate any other unique keys on `child_table` if the foreign key is part of one.

5

Re-attempt the INSERT or UPDATE operation with the corrected data.

3. Temporarily Disable and Re-enable Foreign Key Checks easy

A quick diagnostic step to isolate the issue or allow a bulk operation, but use with extreme caution.

1

Disable foreign key checks for the current session. This allows you to perform operations that might otherwise be blocked by foreign key constraints.

SET foreign_key_checks = 0;

2

Perform the operation that was previously failing. This could be an INSERT, UPDATE, or even a DELETE on a table that has foreign key constraints.

INSERT INTO child_table (col1, parent_id) VALUES ('value1', 'non_existent_or_duplicate_parent_id');

3

Immediately re-enable foreign key checks. **It is crucial to re-enable this as soon as possible to maintain data integrity.**

SET foreign_key_checks = 1;

4

If the operation succeeded while checks were disabled, you have confirmed the issue is related to foreign key constraints. You will still need to address the underlying data inconsistency (e.g., by cleaning up duplicates or correcting references) to prevent future issues.

🔗

Related Errors

5 related errors

MariaDB Error 1761: Foreign Key Duplicate Entry