MySQL - unique constraints design
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.