SQL ON DUPLICATE KEY with 2 unique keys?

Question

I have a MySQL table that goes like so:

+----+--------+-------+-------------+
| id | userID | month | lastUpdated |
+----+--------+-------+-------------+
|  1 |      5 |     1 | 2017-03-27  |
|  2 |      5 |     2 | 2017-03-22  |
|  3 |      5 |     3 | 2017-03-21  |
|  4 |      9 |     1 | 2017-03-27  |
|  5 |      9 |     2 | 2017-03-22  |
|  6 |      9 |     3 | 2017-03-21  |
+----+--------+-------+-------------+

I want to make an INSERT statement to this table but use ON DUPLICATE KEY UPDATE like this:

INSERT INTO users_last_updated
(userID, month, lastUpdated)
VALUES
(:userID, :month, :lastUpdated)
ON DUPLICATE KEY UPDATE lastUpdated = :lastUpdated

The thing is, a userID can show up multiple times and a month value can show up multiple times BUT, the uniqueness of each row is a combination of userID & month (e.g: userID = 1 & month = 3 can only appear once).

Is there a way to make a combination of 2 columns to be unique?

Thanks :)

Create unique index on multiple columns - see http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql — Roman Hocke, Mar 27 '17 at 09:13
But can't there be only 1 PK per table? the AI 'id' is already set as PK — Erez Hod, Mar 27 '17 at 09:13
PK not exactly equals `unique index`. You can have just 1 PK, but multiple unique indexes (composite or single column) per table — Oto Shavadze, Mar 27 '17 at 09:27

score 1 · Accepted Answer · answered Mar 27 '17 at 10:34

If the unique key is userid/month, then use both of those for a unique index or constraint:

create index unq_users_last_updated_2 on users_last_updated(userId, month);

I would add that it seems strange to have month without a year.

The on duplicate key uses any and all available unique indexes, including primary keys (but not limited only to primary keys).

SQL ON DUPLICATE KEY with 2 unique keys?

1 Answers1