MySQL INSERT IGNORE or ON DUPLICATE KEY DO_NOTHING

Question

already searched for such topics and found 2 different solutions but noone works.

My table has structure | ID (auto_increment primary_key) | UID (int) | FAV_ID (int) |

I need to insert new record to this FAV_TABLE if UID and FAV_ID (both) already exist.

Example of my query:

INSERT INTO FAV_TABLE (uid, fav_id) VALUES ($u_id,$s_id) ON DUPLICATE KEY UPDATE  uid = uid

or this one

INSERT IGNORE FAV_TABLE (uid, fav_id) VALUES ($u_id,$s_id);

As mysql manuals says this query doesn't add record only if PRIMARY_KEY is the same. And I need query not to add record if pair uid+fav_id is unique. Any solutions? Thank you

eggyal · Accepted Answer · 2012-04-28 17:02:39Z

5

You need to add a UNIQUE KEY on those columns:

ALTER TABLE FAV_TABLE ADD UNIQUE KEY(uid, fav_id);

answered Apr 28, 2012 at 17:02

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stelian Matei · Accepted Answer · 2012-04-28 17:02:39Z

2

INSERT IGNORE or ON DUPLICATE KEY works only when a unique key is duplicated.

You need to add a UNIQUE index on both fields uid and fav_id. That way, when you insert a duplicate pair, it will be ignored.

answered Apr 28, 2012 at 17:02

Stelian Matei

11.6k3 gold badges28 silver badges29 bronze badges

Collectives™ on Stack Overflow

MySQL INSERT IGNORE or ON DUPLICATE KEY DO_NOTHING

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related