MySQL 1210: Key already exists.
MySQL 1210: Key already exists.
When encountering the error 1210: Key already exists in MySQL, the user should:
- Identify the specific key or row causing the conflict. Run:
SHOW INDEXES FROM table_name;
Replace `table_name` with the name of the table you're working with. This command shows the indexes and their unique properties.
- Check for duplicate entries in the table for the unique key or index identified. For a given unique key `uniquekeyname`, run:
SELECT uniquekeycolumns, COUNT(*)
FROM table_name
GROUP BY uniquekeycolumns
HAVING COUNT(*) > 1;
Replace `uniquekeycolumns` with the columns making up the unique key or index, and `table_name` with the actual name of your table.
- If duplicates are found, decide on the correct action (e.g., delete duplicates, update to make them unique). To delete duplicates while keeping the first entry, you can use:
DELETE t1 FROM table_name t1
INNER JOIN table_name t2
WHERE
t1.id > t2.id AND
t1.uniquekeycolumns = t2.uniquekeycolumns;
Replace `id` with the primary key column and `uniquekeycolumns` with the columns making up the unique key.
- If no duplicates are found, or after cleaning up, ensure applications inserting or updating the database handle key uniqueness properly to prevent future occurrences. This might involve checking application logic or constraints.
- Finally, if the issue persists, consider checking for hidden characters or case sensitivity issues in the key values, which might cause seemingly unique entries to be considered duplicates. Use `HEX()` function to see if there are hidden characters:
SELECT HEX(column_name), COUNT(*)
FROM table_name
GROUP BY HEX(column_name)
HAVING COUNT(*) > 1;
Replace `column_name` with the name of the column suspected of having hidden characters.
Master
MySQL
debugging in Minutes
— Grab the Ultimate Cheatsheet
(Perfect for DevOps & SREs)
MySQL
Cheatsheet
(Perfect for DevOps & SREs)
MORE ISSUES
Backed by
certifed
certified