Changing table field to UNIQUE

Question

I want to run the following sql command:

ALTER TABLE `my_table` ADD UNIQUE (
`ref_id` ,
`type`
);

The problem is that some of the data in the table would make this invalid, therefore altering the table fails.

Is there a clever way in MySQL to delete the duplicate rows?

Answer 1

SQL can, at best, handle this arbitrarily. To put it another way: this is your problem.

You have data that currently isn't unique. You want to make it unique. You need to decide how to handle the duplicates.

There are a variety of ways of handling this:

• Modifying or deleting duplicate rows by hand if the numbers are sufficiently small;
• Running statements to update or delete duplicate that meet certain criteria to get to a point where the exceptions can be dealt with on an individual basis;
• Copying the data to a temporary table, emptying the original and using queries to repopulate the table; and
• so on.

Note: these all require user intervention.

You could of course just copy the table to a temporary table, empty the original and copy in the rows just ignoring those that fail but I expect that won't give you the results that you really want.