MySQL: ALTER IGNORE TABLE ADD UNIQUE, what will be truncated?

by

The first record will be kept, the rest deleted §§:

IGNORE is a MySQL extension to
standard SQL. It controls how ALTER
TABLE works if there are duplicates on
unique keys in the new table or if
warnings occur when strict mode is
enabled. If IGNORE is not specified,
the copy is aborted and rolled back if
duplicate-key errors occur. If IGNORE
is specified, only the first row is
used of rows with duplicates on a
unique key, The other conflicting rows
are deleted. Incorrect values are
truncated to the closest matching
acceptable value

I am guessing ‘first’ here means the one with the smallest ID, assuming the ID is the primary key.

Also note:

As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.

More Related Contents:

  1. add column to mysql table if it does not exist
  2. difference between primary key and unique key
  3. Difference between Key, Primary Key, Unique Key and Index in MySQL
  4. INSERT … ON DUPLICATE KEY (do nothing)
  5. MySQL: ALTER TABLE if column not exists
  6. Adding multiple columns AFTER a specific column in MySQL
  7. How can I modify the size of column in a MySQL table?
  8. Error renaming a column in MySQL
  9. ALTER TABLE to add a composite primary key
  10. How to add not null constraint to existing column in MySQL
  11. Alter MySQL table to add comments on columns
  12. Create an index on a huge MySQL production table without table locking
  13. ALTER table – adding AUTOINCREMENT in MySQL
  14. How to change MySQL column definition?
  15. Alter table if exists or create if doesn’t
  16. `MODIFY COLUMN` vs `CHANGE COLUMN`
  17. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  18. How do I add more members to my ENUM-type column in MySQL?
  19. Optimizing MySQL for ALTER TABLE of InnoDB
  20. Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)
  21. Search for all occurrences of a string in a mysql database [duplicate]
  22. MySQL Aggregate Functions without GROUP BY clause
  23. Count days between two dates, excluding weekends (MySQL only)
  24. Is there a way to “limit” the result with ELOQUENT ORM of Laravel?
  25. Fixing “Lock wait timeout exceeded; try restarting transaction” for a ‘stuck” Mysql table?
  26. rails + MySQL on OSX: Library not loaded: libmysqlclient.18.dylib
  27. ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ [duplicate]
  28. Storing images in MySQL [closed]
  29. Would it help to add index to BIGINT column in MySQL?
  30. What happens when I exhaust a bigint generated key? How to handle it?

Leave a Comment