Multiple-attr unique constraint prevents row insertion due to "duplicate" error

Question

This is what my table looks like:

   id   user_id event_id    
    4   0       10 
    3   1       8 
    1   2       8 
    2   3       8 
    5   4       10 
    6   5       5 

I've applied the unique constraint on (user_id, event_id) combination (multiple users can be invited to multiple events, but a user cannot be invited to the same event twice):

ALTER TABLE `Event_Invited` ADD Unique(`event_id`, `user_id`)

I am trying to insert a new record/row with values event_id = 8 and user_id = 4 Which should be fine, but I am getting the error:

"Duplicate entry '4' for key 'user_id_3'  

Why? At first I thought I accidentally set a UNIQUE constraint specifically to the users_id column, but even after I made sure I have that removed, I am still getting these errors.

Edit: It seems as though I am getting this error because I have some constraint not allowing me to have records with the same user_id value. Does the constraint I specified above makes the values in each column unique, rather than making their combination unique?

Answer 1

ALTER TABLE `Event_Invited` ADD Unique index(`event_id`, `user_id`)

Check out 13.1.8. CREATE INDEX Syntax for more