📋

Description

This error occurs when an operation (like INSERT or UPDATE) on a child table attempts to create or modify a record such that the foreign key value, combined with other columns, would violate a unique constraint on the child table. It indicates that the database prevented a duplicate entry from being created where uniqueness is expected based on the table's schema and foreign key relationship.

💬

Error Message

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

🔍

Known Causes

3 known causes

⚠️

Duplicate Foreign Key Value on Insert

An INSERT statement attempts to add a new record to the child table with a foreign key value (or a combination of foreign key and other columns) that matches an existing unique entry in the child table.

⚠️

Updating to an Existing Unique Key

An UPDATE statement modifies an existing child record, setting its foreign key value (or related unique-indexed columns) to one that already exists in another record within the same child table, violating a unique constraint.

⚠️

Bulk Data Import Conflict

During a bulk import or data migration, multiple records are inserted into the child table that contain identical foreign key values (and possibly other unique-indexed columns), leading to a unique constraint violation.

🛠️

Solutions

4 solutions available

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

Find and delete duplicate rows in the table that the foreign key points to.

1

Identify the tables and the key involved in the error message. The error message provides '%s' placeholders for the table names, the record causing the issue, and the key that would have a duplicate.

2

Determine which column(s) constitute the unique key in the referenced table. This is usually the primary key or a unique index.

DESCRIBE referenced_table_name;

3

Find duplicate entries in the referenced table based on the columns that should be unique. Replace `column1`, `column2`, etc., with the actual column names of the unique key.

SELECT column1, column2, COUNT(*) FROM referenced_table_name GROUP BY column1, column2 HAVING COUNT(*) > 1;

4

Carefully examine the identified duplicate records. Decide which record to keep and which to remove. This might involve comparing timestamps, other data, or simply selecting one arbitrarily if the data is identical.

5

Delete the duplicate records from the referenced table. Ensure you are deleting the correct records. It's highly recommended to back up the table before performing deletions.

DELETE FROM referenced_table_name WHERE primary_key_column = duplicate_id_to_remove;

6

Retry the operation that caused the original error.

2. Temporarily Disable Foreign Key Checks easy

Skip foreign key constraint checks for a specific operation.

1

Disable foreign key checks for the current session.

SET foreign_key_checks = 0;

2

Perform the operation that was causing the error (e.g., INSERT, UPDATE, DELETE).

INSERT INTO your_table (column1, column2) VALUES (value1, value2);

3

Re-enable foreign key checks immediately after the operation.

SET foreign_key_checks = 1;

3. Adjust Data Being Inserted/Updated medium

Modify the data you are trying to insert or update to align with existing unique keys.

1

Analyze the record that is causing the foreign key constraint violation. The error message provides details about the record ('%s').

2

Examine the referenced table and its unique keys. Identify the specific entry in the referenced table that would be duplicated by your operation.

SELECT * FROM referenced_table_name WHERE unique_key_column = 'value_causing_duplicate';

3

Modify the data in your operation so that it does not attempt to create a duplicate entry in the referenced table. This might involve changing a value in the foreign key column to match an existing, non-duplicate entry.

INSERT INTO your_table (foreign_key_column, other_column) VALUES (existing_valid_value, 'some_other_data');

4

Alternatively, if you are updating a record, ensure the new foreign key value points to a valid and non-duplicating entry.

UPDATE your_table SET foreign_key_column = new_valid_value WHERE id = record_to_update_id;

4. Alter the Table to Remove or Modify the Foreign Key Constraint advanced

Remove or change the foreign key constraint if it's no longer needed or incorrectly defined.

1

Identify the foreign key constraint name. You can find this by describing the table or querying the information schema.

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name' AND REFERENCED_TABLE_NAME = 'referenced_table_name';

2

Drop the foreign key constraint. This will permanently remove the constraint, so proceed with caution and ensure you understand the implications.

ALTER TABLE your_table_name DROP FOREIGN KEY constraint_name;

3

If you intend to re-add the constraint later with different rules, or if the constraint is incorrect, you might consider modifying it instead. However, this is often more complex and may involve recreating the table.

4

After dropping or altering the constraint, you can perform your operation. Consider re-adding the constraint with appropriate data or logic if necessary.

🔗

Related Errors

5 related errors

MySQL Error 1761: Foreign Key Duplicate Entry