CODEWITHSUNDEEP
mysqlsql
Erez Hod
Erez Hod

Reputation: 1883

SQL ON DUPLICATE KEY with 2 unique keys?

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 :)

Upvotes: 0

Views: 1947

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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

Upvotes: 1

Related Questions