MySQL "Insert ... On Duplicate Key" with more than one unique key

Question

I've been reading up on how to use MySQL insert on duplicate key to see if it will allow me to avoid Selecting a row, checking if it exists, and then either inserting or updating. As I've read the documentation however, there is one area that confuses me. This is what the documentation says:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed

The thing is, I don't want to know if this will work for my problem, because the 'condition' I have for not inserting a new one is the existence of a row that has two columns equal to a certain value, not necessarily that the primary key is the same. Right now the syntax I'm imagining is this, but I don't know if it will always insert instead of replace:

INSERT INTO attendance (event_id, user_id, status) VALUES(some_event_number, some_user_id, some_status) ON DUPLICATE KEY UPDATE status=1

The thing is, event_id and user_id aren't primary keys, but if a row in the table 'attendance' already has those columns with those values, I just want to update it. Otherwise I would like to insert it. Is this even possible with ON DUPLICATE? If not, what other method might I use?

Solution 1

If I were you, I would make a primary key out of event_id and user_id. That will make this extremely easy with ON DUPLICATE.

SQLFiddle

create table attendance (
    event_id int,
    user_id int,
    status varchar(100),
    primary key(event_id, user_id)
);

Then with ease:

insert into attendance (event_id, user_id, status) values(some_event_number, some_user_id, some_status)
on duplicate key
update status = values(status);

Solution 2

The quote includes "a duplicate value in a UNIQUE index". So, your values do not need to be the primary key:

create unique index attendance_eventid_userid on attendance(event_id, user_id);

Presumably, you want to update the existing record because you don't want duplicates. If you want duplicates sometimes, but not for this particular insert, then you will need another method.

Solution 3

Maybe you can try to write a trigger that checks if the pair (event_id, user_id) exists in the table before inserting, and if it exists just update it.

Solution 4

To the broader question of "Will INSERT ... ON DUPLICATE respect a UK even if the PK changes", the answer is yes: SQLFiddle

In this SQLFiddle I insert a new record, with a new PK id, but its values would violate the UK. It performs the ON DUPLICATE and the original PK id is preserved, but the non-UK ON DUPLICATE KEY UPDATE value changes.

View more solutions

Comments

[phouse512]

what is "primary key(event_id, user_id)" doing? Does it generate a primary key from that?

[Tricky12]

It makes the pair of event_id and user_id your primary key. So if you are inserting with the same event_id AND user_id it will update, otherwise it will insert. ONLY if they BOTH match, not just event_id or just user_id. I added an SQLFiddle so you can check it out.

[phouse512]

in the answer below, Chad used a primary key in a similar way. Could I make a unique index without creating another column in the same way that he did below?

[phouse512]

so it doesn't add an extra physical column?

[Gordon Linoff]

You can create either a unique index or a primary key on the columns. Because almost all tables I create have an auto-incremented primary key, I would use a unique index.

[Tricky12]

Not that you will get from a query, no. It is just telling the table to use the combination of the event_id column and the user_id column as the key, instead of just a single column.