Indirect constraints (foreign keys amongst three/four tables) [duplicate]

Question

I have the next layout of tables:

table COMPANY:
- primary key pk_company

table USERS:
- primary key pk_users, 
- foreign key (fk_company) references COMPANY (pk_company)

table GROUPS: <br>
- primary key pk_group,
- foreign key (fk_company) references COMPANY (pk_company)

table ROLES:
- primary key pk_role

table RELATIO_UGR:
- foreign key (fk_user) references USERS (pk_user)
- foreign key (fk_group) references GROUPS (pk_group)
- foreign key (fk_role) references ROLES (pk_role)
- Also: UNIQUE KEY [fk_user,fk_group] 

This is:

USER ---(n:m)--- GROUP ---(m:1)--- COMPANY
USER ------------(n:1)------------ COMPANY
1 company can have N users (workers)
1 company can have M groups
M groups can have N users (each worker can be in more than one group)
1 user can have only 1 role (read / read-write / admin) in each group.

The thing is that all constraints seem to work in the individual tables, however, the RELATIO_UGR table fails to establish constraints.

Let's suppose we have:

• USER1 from GROUP1 and COMPANY1
• USER2 from GROUP2 and COMPANY2

The RELATIO_UGR shouldn't allow me to establish (for example) the relationship:

• USER2-GROUP1-COMPANY2

Yet, I CAN insert it...

How do I restrict it?

Answer 1

Your business rules will dictate your schema modeling. It is important you establish all these rules first. If a user can belong to multiple Group (group 1 , group 2, group3) than it is the rule. If you don't want a user to be able to be inserted in a group that belongs to another company, you need to program this logic at the application layer. FK itself only checks the key exists in parent on insertion. (it does not verify for you if that user is from group 1 or 2). If you want to enforce this, i would recommend using triggers and using a userGroup table:

USER ------------(n:1)------------ COMPANY
USER ---(n:m)--- UserGROUP --(n:m)---- Group---(m:1)--- COMPANY

UserGROUP (group_id,user_id, roleId) 
(PK on group_id, userId to enforce 1 user role per group) 

Trigger On insert in UserGROUP:
 if user.company_id = group_company_id, then insert else "error".