I am using MySQL and looking to solve a problem with relational data. I am not sure it is possible but wanted to ask the community.

Please consider the diagram:

enter image description here

I would like to add a unique constraint such that only one User can have only one Role per Event (Which is stored in the UserEventRoles table). Not sure how this can be solved? Perhaps with different design? Also the goal is not have a scenario where keys can be "out of sync", such as users role would point to an event where that role does not exist.

EventRoles table describes which Roles are valid at each Event.

I am sure this problem has come up before with other database designs and would like know what the best approach may be.

Also here is a link to sql fiddle to play around schema and some sample data.


  • Do you mean that any user cannot have more than one role per event? (that's @Willem's assumption and answer) Aug 16, 2013 at 20:21
  • Or that in any event there cannot be more than one users with same role? (this seems weird as you could not have more than one Attendees in an Event.) Aug 16, 2013 at 20:21
  • @ypercube: A single user may only have a single role at any event.
    – Michael
    Aug 16, 2013 at 20:45

2 Answers 2


This is fairly simple to accomplish by making some changes to your primary keys.


I changed the primary key of the UserEventRoles table to be a composite key of user_id and event_id. This forces each User to only ever have at most one Role per Event.

I also modified the EventRoles table to use composite primary key. The surrogate key provided no value and made the resulting query more complex by forcing you to join into the EventRoles table if you wanted to preserve the foreign key constraint between that table and UserEventRoles.

With this change, you make sure you never define a role twice at an event, and you can directly join the columns in the UserEventRoles table to the descriptive tables that actually contain data you want.

  • Composite keys - FTW! This exactly what I was looking for. Solves the problem perfectly.
    – Michael
    Aug 16, 2013 at 20:46

IMHO having both EventRoles and UserEventRoles are too much.

I'd propose to

  1. move user_id column to EventRoles
  2. create a unique constraint on (event_id, user_id)
  3. optionally make user_id nullable to be able to lay out roles for event without assigning them to particular persons
  4. get rid of UserEventRoles

Now to get your list

SELECT e.name event_name, 
       e.date event_date,
       r.name role, 
       u.name user_name
  FROM eventroles er JOIN events e
    ON er.event_id = e.id JOIN roles r
    ON er.role_id = r.id LEFT JOIN users u
    ON er.user_id = u.id

Note that INNER JOINs are used to join eventroles with ref tables events and roles. LEFT JOIN is only used to connect to users and only because I made user_id nullable.

Sample output:

| EVENT_NAME |                       EVENT_DATE |     ROLE | USER_NAME |
|  Beer Fest |    August, 15 2013 00:00:00+0000 | Attendee |       Bob |
|  Beer Fest |    August, 15 2013 00:00:00+0000 | Security |    (null) | -- not assigned yet
|  Beer Fest |    August, 15 2013 00:00:00+0000 | Security |    (null) | -- not assigned yet
|  Beer Fest |    August, 15 2013 00:00:00+0000 | Security |       Joe |
|  RailsConf | September, 15 2013 00:00:00+0000 |    Staff |     Sally |
|  RailsConf | September, 15 2013 00:00:00+0000 | Security |       Joe |

Here is SQLFiddle demo.
You can try to uncomment the last two insert statements and see that a defined unique constraint will prevent them from being successfully completed

  • 1
    But it allows to insert VALUES (1,4,1) so Joe would be both "Bartender" and "Security" at the same "Beer Fest" event. Aug 16, 2013 at 19:41
  • @ypercube Fair enough. Thanks for the input. It's fixed. Answer and sqlfiddle updated.
    – peterm
    Aug 16, 2013 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.