如果列不唯一，如何更新重复键？

Question

How do I insert only when the 2 row values are not the same exactly? Here is my scenario so my question makes sense:

ID      employee_id     client_id
1           1              10
2           2              11
3           1              11
4           2              10

I want to insert something like this to the column in PDO:

insert into yourTableName (employee_id, client_id)
    values (:employee_id, :client_id)
    on duplicate key update 
        employee_id=:$employee_id2, client_id=:client_id2

But the insert on duplicate above only seems to work if one of them columns were unique. In my case, the values in employee_id and client_id are not unique and the values can repeat multiple times. However there can only be 1 combination of it.

How can I only insert the row only if the exact combination of column values doesnt exit?

Answer 1

INSERT ... ON DUPLICATE KEY UPDATE ... requires a unique key. The unique key can be on multiple columns. Simply create a multi-column unique key on both columns (employee_id, client_id).

There is a caveat to using INSERT ... ON DUPLICATE KEY UPDATE .... With default settings, if ID is an auto-increment column, the counter will increment each time you call INSERT ... ON DUPLICATE KEY UPDATE ... even if it just updates because the next id is generated first. If that is a problem, you should use SELECT FOR UPDATE within a transaction to see if the record exists, then behave accordingly.