Error
Error Code:
1557
MariaDB Error 1557: Foreign Key Duplicate Entry
Description
This error indicates that an operation, such as inserting or updating data, would violate a foreign key constraint because the referenced primary or unique key in the parent table already contains duplicate entries. It signifies a fundamental data consistency issue within the parent table that prevents the foreign key from being upheld.
Error Message
Upholding foreign key constraints for table '%s', entry '%s', key %d would lead to a duplicate entry
Known Causes
3 known causesParent Key Uniqueness Violated
The unique index or primary key in the parent table, which the foreign key references, contains duplicate values, violating its own uniqueness.
Missing Parent Unique Constraint
The parent table lacks a proper UNIQUE or PRIMARY KEY constraint on the column(s) referenced by the foreign key, allowing duplicate entries to exist.
Data Corruption in Parent Table
Underlying data corruption or unhandled database operations have led to the referenced unique key in the parent table having duplicate values.
Solutions
3 solutions available1. Identify and Remove Duplicate Foreign Key Entries medium
Locate and delete the conflicting duplicate entries in the referencing table.
1
The error message indicates a duplicate entry is being inserted or updated that violates a foreign key constraint. The table name, the offending entry (likely a value being inserted), and the key number are provided in the error message. Use this information to construct a query to find the existing entry in the referencing table that matches the new entry.
SELECT * FROM referencing_table WHERE column_name = 'offending_value';
-- Replace 'referencing_table' with the actual table name from the error message.
-- Replace 'column_name' with the column that has the foreign key constraint.
-- Replace 'offending_value' with the entry value from the error message.2
Once you've identified the duplicate row(s) in the `referencing_table`, you need to determine if they are genuinely duplicates that should be removed or if there's a misunderstanding of the data. If they are indeed duplicates and shouldn't exist, delete them.
DELETE FROM referencing_table WHERE primary_key_column = 'primary_key_of_duplicate_row';
-- Replace 'referencing_table' with the actual table name.
-- Replace 'primary_key_column' with the primary key of the referencing table.
-- Replace 'primary_key_of_duplicate_row' with the primary key value of the identified duplicate row.3
After removing the duplicate, retry the original operation that caused the error (e.g., INSERT or UPDATE).
2. Modify the Data Being Inserted/Updated easy
Adjust the data to ensure it doesn't create a duplicate foreign key entry.
1
Examine the data you are attempting to insert or update into the `referencing_table`. The error message '%s', entry '%s', key %d' provides clues. The '%s' (entry) is the value causing the conflict. Ensure this value is unique within the foreign key column if the constraint is meant to enforce uniqueness (e.g., if the foreign key column is also a unique key or part of a unique index).
2
If the value is intended to be unique and you are trying to insert a duplicate, modify the value to be unique. If the value should not be unique, review your table schema and constraints.
3
If the foreign key column is part of a unique index on the `referencing_table` and the value you are trying to insert already exists, you might need to update the existing row instead of inserting a new one, or choose a different value.
3. Temporarily Disable and Re-enable Foreign Key Checks medium
Disable checks to perform the operation, then re-enable and fix the data.
1
Temporarily disable foreign key checks for the current session. This allows you to perform operations that might otherwise fail due to constraint violations. Be cautious with this approach, as it bypasses data integrity checks.
SET foreign_key_checks = 0;2
Perform the `INSERT` or `UPDATE` operation that was failing. Since foreign key checks are disabled, it should now succeed, even if it creates a temporary inconsistency.
INSERT INTO referencing_table (column1, column2, foreign_key_column) VALUES (value1, value2, offending_value);
-- Or your failing UPDATE statement.3
Immediately re-enable foreign key checks. This is crucial to restore data integrity.
SET foreign_key_checks = 1;4
Now, you must address the underlying issue. Use the steps from 'Identify and Remove Duplicate Foreign Key Entries' or 'Modify the Data Being Inserted/Updated' to correct the data so that it conforms to the foreign key constraints when checks are enabled.