MySQL – unique constraints design

constraintdatabase-designMySQLunique-constraint

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.

sqlFiddle

Best Answer

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

http://sqlfiddle.com/#!2/8dcdd/1

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.

Related Question