MySQL INSERT IGNORE not working

Question

Here's my table with some sample data

a_id | b_id
------------
  1    225
  2    494
  3    589

When I run this query

INSERT IGNORE INTO table_name (a_id, b_id) VALUES ('4', '230') ('2', '494')

It inserts both those rows when it's supposed to ignore the second value pair (2, 494)

No indexes defined, neither of those columns are primary.

What don't I know?

Answer 1

if there is no primary key, there can't be duplicate key to ignore. you should always set a primary key, so pleae do that - and if you want to have additional colums that shouldn't be duplicate, set them as "unique".

Answer 2

If you don't put a UNIQUE criteria or set a PRIMARY KEY, MySql won't know that your new entry is a duplicate.

Answer 3

From the docs:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.

(my italics).

Your row is not duplicating "an existing UNIQUE index or PRIMARY KEY value" since you have no primary key nor any unique constraints.

---

If, as you mention in one of your comments, you want neither field to be unique but you do want the combination to be unique, you need a composite primary key across both columns (get rid of any duplicates first):

alter table MYTABLE add primary key (a_id,b_id)

Answer 4

If I understand you correctly, after you run the insert command your table looks like this

1   225
2   494
3   589
4   230
2   494

If so, then the answer is because your table design allows duplicates.

If you want it prevent the second record from being inserted, you'll need to define the a_id column as a primary key, or a unique index. If you do, then the insert ignore statement will work as you expect it to, i.e. insert the records, ignore the errors such as trying to add a duplicate record.