MySQL: ALTER IGNORE TABLE gives “Integrity constraint violation”

by

The IGNORE keyword extension to MySQL seems to have a bug in the InnoDB version on some version of MySQL.

You could always, convert to MyISAM, IGNORE-ADD the index and then convert back to InnoDB

ALTER TABLE table ENGINE MyISAM;
ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field);
ALTER TABLE table ENGINE InnoDB;

Note, if you have Foreign Key constraints this will not work, you will have to remove those first, and add them back later.

More Related Contents:

  1. How do I specify unique constraint for multiple columns in MySQL?
  2. Does MySQL ignore null values on unique constraints?
  3. MySQL – Make an existing Field Unique
  4. SQL unique varchar case sensitivity question
  5. How to use IN & NOT IN same query [closed]
  6. SQL Print the name of all Employees together with the name of their supervisor
  7. Syntax error due to using a reserved word as a table or column name in MySQL
  8. Change MySQL default character set to UTF-8 in my.cnf?
  9. MySQL: Large VARCHAR vs. TEXT?
  10. MySQL offset infinite rows
  11. Multiple-column foreign key in MySQL?
  12. What is the question mark’s significance in MySQL at “WHERE column = ?”?
  13. How do write IF ELSE statement in a MySQL query
  14. mysql – making a mechanism similar to Oracle’s seqences
  15. How do you uninstall MySQL from Mac OS X?
  16. Difference between VARCHAR and TEXT in MySQL [duplicate]
  17. Get the latest date from grouped MySQL data
  18. How to program a MySQL trigger to insert row into another table?
  19. UUID performance in MySQL?
  20. MySQL: NULL vs “”
  21. Invalid default value for ‘dateAdded’
  22. Importing a CSV to MySQL with different date format
  23. MySQL too long varchar truncation/error setting
  24. how to use DISTINCT ON with mysql using ActiveRecord
  25. use a variable for table name in mysql sproc
  26. How to create dummy variable columns for thousands of categories in Google BigQuery?
  27. Most efficient way to store IP Address in MySQL [duplicate]
  28. What is the best way to do incremental backups in MySQL?
  29. MySQL’s lower_case_table_names won’t change
  30. Optimistic locking in MySQL

Leave a Comment