How to Force a Unique Index on a MySQL Table

The simple answer is use “Ignore” instead of “Force”.

This is being posted for one reason, I always try, and error out with a force instead of using ignore. This scenario applies when you want to weed out the duplicate entries, MySQL will error out if there are duplicate entries, you have to ignore simply to give MySQL permission to remove those dups. Here is an example….

To apply a unique index to an existing table (with dups) use:
ALTER IGNORE TABLE `sites` ADD UNIQUE (`url`)

Do NOT use:
FORCE ALTER TABLE `sites` ADD UNIQUE (`url`)
or
ALTER TABLE FORCE `sites` ADD UNIQUE (`url`)
or
ALTER FORCE TABLE `sites` ADD UNIQUE (`url`)

Yes, this is all basic stuff. I just want to help others making this same elementary mistake. It also helps to know if I get in the same jam a year from now, I have my own blog to search for the answer.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>