Combinationally unique MySQL tables
- by Jack Webb-Heller
So, here's the problem (it's probably an easy one :P)
This is my table structure:
CREATE TABLE `users_awards` (
`user_id` int(11) NOT NULL,
`award_id` int(11) NOT NULL,
`duplicate` int(11) NOT NULL DEFAULT '0',
UNIQUE KEY `award_id` (`award_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
So it's for a user awards system. I don't want my users to be granted the same award multiple times, which is why I have a 'duplicate' field.
The query I'm trying is this (with sample data of 3 and 2) :
INSERT INTO users_awards (user_id, award_id)
VALUES ('3','2') ON DUPLICATE KEY UPDATE duplicate=duplicate+1
So my MySQL is a little rusty, but I set user_id to be a primary key, and award_id to be a UNIQUE key. This (kind of) created the desired effect.
When user 1 was given award 2, it entered. If he/she got this twice, only one row would be in the table, and duplicate would be set to 1. And again, 2, etc.
When user 2 was given award 1, it entered. If he/she got this twice, duplicate updated, etc. etc.
But when user 1 is given award 1 (after user 2 has already been awarded it), user 2 (with award 1)'s duplicate field increases and nothing is added to user 1.
Sorry if that's a little n00bish. Really appreciate the help!
Jack
