CODEWITHSUNDEEP
mysqlinsertduplicates
HyderA
HyderA

Reputation: 21411

MySQL INSERT IGNORE not working

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?

Upvotes: 12

Views: 21426

Answers (4)

paxdiablo
paxdiablo

Reputation: 882646

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)

Upvotes: 20

pdwalker
pdwalker

Reputation: 863

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.

Upvotes: 0

Julien Hoarau
Julien Hoarau

Reputation: 50000

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

Upvotes: 3

oezi
oezi

Reputation: 51817

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".

Upvotes: 1

Related Questions