How to enforce integrity when joined tables share a foreign key?

Question

Consider the following table structure:

UserGroup(ie. 'Admin')
    ID = 'Admin'
    ApplicationFK = 'App1'

JoinTable
    UserGroupFK = 'Admin'
    PermissionFK = 'Approve'
    PermissionValue = 'READ'

Permission(ie. 'Approve')
    ID = 'Approve'
    ApplicationFK = 'App2'

UserPermission
    UserFK = 'John'
    PermissionFK = 'Approve'
    PermissionValue = 'WRITE'

User
    ID = 'John'

Where you have a jointable between UserGroup and Permission, with both UserGroup and Permission having foreign keys to Application.

Is there any way to enforce integrity, to avoid the situation above, where the ApplicationFKs don't match? I cannot simply remove one of the Foreign Keys, as it is possible to have a Permission without any UserGroups, and possible to have a UserGroup without any permissions.

Yes, I call this the "diamond" problem. Check any of these questions/answers: diamond — ypercubeᵀᴹ, Jul 06 '17 at 21:34
The "trick" is to add ApplicationFK to the lowest table in the diamond (JoinTable in this case) and modify the Foreign Key and Unique constraints accordingly. — ypercubeᵀᴹ, Jul 06 '17 at 21:35
@ypercubeᵀᴹ I've added additional information about related tables I'd previously thought unnecessary. I've also created an answer, attempting to confirm I've correctly understood your suggestion - is it correct? — Sarov, Jul 07 '17 at 13:57
No, it doesn't look what I has thinking of. I'll add an answer later tonight. — ypercubeᵀᴹ, Jul 07 '17 at 14:00
@ypercubeᵀᴹ Updated below answer after some more thought. — Sarov, Jul 07 '17 at 14:24

Sarov · Accepted Answer · 2017-07-07T16:55:33.303

1

Making sure I understand/have correctly elaborated on @ypercubeᵀᴹ's comment/linked answer:

The correct structure should be:

UserGroup(ie. 'Admin')
    ID = 'Admin'
    ApplicationFK = 'App1'
    [PK: ID, ApplicationFK]

JoinTable
    UserGroupFK = 'Admin'
    ApplicationFK = 'App1'
    PermissionFK = 'Approve'
    PermissionValue = 'READ'
    [PK: UserGroupFK, ApplicationFK, PermissionFK]
    [FK -> UserGroupFK+ApplicationFK -> UserGroup]
    [FK -> PermissionFK+ApplicationFK -> Permission]

Permission(ie. 'Approve')
    ID = 'Approve'
    ApplicationFK = 'App1'
    [PK: ID, ApplicationFK]

UserPermission
    UserFK = 'John'
    PermissionFK = 'Approve'
    ApplicationFK = 'App1'
    PermissionValue = 'WRITE'
    [FK -> PermissionFK+ApplicationFK -> Permission]

User
    ID = 'John'

edited Jul 07 '17 at 16:55

answered Jul 07 '17 at 13:56

Sarov

279
2
10

The row you show in JoinTable would be invalid, as you don't show a Permission row ('Approve','App1') – RDFozz Jul 07 '17 at 15:47
Also - Wherever you're tying to Permission, you need to provide the full key: the permission ID and the application ID. The only real difference between UserPermission and JoinTable is one should assign permissions for an application to a user, and the other to a user group. – RDFozz Jul 07 '17 at 15:51

score 0 · Answer 2 · answered Jul 06 '17 at 21:37

0

The question will be solved after you define primary keys correctly.

Please remember that FK must point to other table's primary key (or unique key).

It is legal (at least in SQL) to use multi-field FKs.

answered Jul 06 '17 at 21:37

filiprem

6,539
1
18
31

How to enforce integrity when joined tables share a foreign key?

2 Answers2