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.
Yancy Lent - Blog
Links
Categories
- Books
- Career
- Cycling
- Family
- Ideas
- Ironman
- Lotus
- Lotus Notes
- Lotusphere
- PanMass Challenge
- Planet Lotus
- Random
- Running
- SharePoint
- Social Media
- Tech
- Triathlon
- Web Development
- WordPress
Archive
- February 2013
- September 2012
- August 2012
- June 2012
- March 2012
- February 2012
- December 2011
- October 2011
- September 2011
- April 2011
- January 2011
- November 2010
- October 2010
- September 2010
- August 2010
- July 2010
- June 2010
- May 2010
- March 2010
- February 2010
- January 2010
- December 2009
- November 2009
- October 2009
- September 2009
- August 2009
- July 2009
- June 2009
- May 2009
- April 2009
- February 2009
- January 2009
- December 2008
- November 2008
- October 2008
- September 2008
- August 2008
- July 2008
- June 2008
- May 2008
- April 2008
- March 2008
- February 2008
- January 2008