MySQL 1206: Error in unique key constraint.
MySQL 1206: Error in unique key constraint.
- Identify the table and column(s) involved in the unique key constraint violation. Check the error message for details.
- Run the following SQL query to find duplicate entries in the specified column(s):
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;
Replace `columnname` with the name of the column(s) involved in the unique key constraint and `tablename` with the name of the table.
- Review the results to identify the duplicate entries.
- Decide on the necessary action for the duplicates: delete, update, or ignore. If updating or deleting, use queries like the following (ensure to backup or export critical data before making changes):
- To delete duplicates while keeping one instance:DELETE t1 FROM table_name t1
INNER JOIN table_name t2
WHERE
t1.id > t2.id AND
t1.columnname = t2.columnname;
- To update duplicates to make them unique (adjust the query based on your specific needs):UPDATE table_name
SET columnname = CONCAT(columnname, '_duplicate', id)
WHERE id IN (
SELECT id FROM (
SELECT id FROM table_name
WHERE column_name IN (
SELECT columnname FROM tablename
GROUP BY column_name
HAVING COUNT(*) > 1
)
) AS subquery
);
Note: The above update command is an example. You should tailor the SQL command based on your actual table structure and how you want to resolve duplicates.
- After resolving the duplicates, try re-running the operation that caused the error to confirm it's resolved.
6. Review your application logic or data entry processes to prevent this type of error in the future.
MySQL
MySQL
MORE ISSUES
Backed by
certifed
certified