sql - Have table be a FK three times to main table - error -

sql - Have table be a FK three times to main table - error -

i trying wrap head around why cannot following, or why not working.

i have table users, within table have alternative user take 3 roles named role1, role2, role3.

i figured create single roles table, contain possible roles (id + rolename), whenever seek add together fk relationship linking role2/role3 roles table id, error saying cannot create foreign key ___ set cascade referential. works fine when doing role1, adding additional fk's role2/role3 not work.

do need create table of roles each instance, or why not work? can tell issue (just instinct), can't pinpoint in head... still new databasing well.

thank you!

using: sql server studio management 2012

edit: in image, both role2 , champ2/3 give me errors when seek have relationships delete/update set on cascade, when set on no action works fine

this problem occurring because of multiple cascade path. giving 2 suggestions per question.

option1:

this not way design database...i suggest create link table between roles table , users table. can store roles along userid in link table overcome problem

first table

roleid(pk) rolename

second table (link table)

linkid(pk) userid roleid

third table

userid(pk) username first table , sec table have 1 many relationship via roleid. third table , sec table have 1 many relationship via userid.

option2:

if not want create link table have option. can create fk between roles table , users without including cascading update , cascade delete , can handle update , deletion using triggers

sql sql-server