In this blog post, let’s learn about the error message “371 Cannot create unique index on column with duplicate data.” in Hydra and the description of the error.
Error Message
371 Cannot create unique index on column with duplicate data.
Error Details
This CREATE UNIQUE INDEX statement cannot be completed because the column (or columns) contains one or more duplicate rows. You can either create an ordinary index, accepting the duplicate values, or you can modify the table to remove the duplicates. To get a list of the duplicate values in a single column, first create the ordinary index. Then use a SELECT statement such as the following, filling in the table and column names:
SELECT column FROM table main WHERE 1 < ( SELECT COUNT(*) FROM table sub WHERE main.column = sub.column )
This statement can be extended to handle the case of multiple columns using AND.