38

I have a table with 4 columns: ID, type, owner, description. ID is AUTO_INCREMENT PRIMARY KEY and now I want to:

ALTER IGNORE TABLE `my_table`
    ADD UNIQUE (`type`, `owner`);

Of course I have few records with type = 'Apple' and owner = 'Apple CO'. So my question is which record will be the special one to stay after that ALTER TABLE, the one with smallest ID or maybe the one with biggest as the latest inserted?

kuba
  • 3,670
  • 3
  • 31
  • 41

2 Answers2

51

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.

Pacerier
  • 86,231
  • 106
  • 366
  • 634
Galz
  • 6,713
  • 4
  • 33
  • 39
  • I've heard it oft said that (SQL) databases have no inherent order, so when selecting records without an ORDER BY clause, we get an order that for our purposes is random, depending on the machinations by which MySQL retrieves the data from disk/memory. So I would guess that 'first' is 'the first MySQL encounters' meaning a more-or-less random one. – user151841 Aug 18 '15 at 20:14
  • This isn't working on BLOB/TEXT column. it gives error "BLOB/TEXT column 'name' used in key specification without a key length" – Dashrath Nov 06 '16 at 10:36
  • 1
    @Dashrath - this error is not related to the subject of this question. Read the manual on BLOB columns creation and indexing. http://dev.mysql.com/doc/refman/5.7/en/blob.html – Galz Nov 07 '16 at 17:42
4

It appears that your problem is one of the very reasons that ALTER IGNORE has been deprecated.

This is from the MySQL notes on the ALTER IGNORE deprecation:

"This feature is badly defined (what is the first row?), causes problems for replication, disables online alter for unique index creation and has caused problems with foreign keys (rows removed in parent table)."

Sally Levesque
  • 111
  • 9