如何根据一个表的递归属性更新多对多关系的链接表？

Question

在我的应用程序中，我使用基于角色的访问控制来对用户进行身份验证和授权。可以为每个用户分配多个角色，这些角色提供对权限的访问。

每个角色都是层次结构的一部分，其中经过验证的角色位于树的顶部。所有其他角色都使用表中的自引用关系从它继承。“默认角色”( default roles )表格如下所示，其中一个“版主”和“开发人员”角色继承自“已验证”，一个“管理员”角色继承自“版主”。

roles table
id name       parent role
1  verified   none
2  moderator  verified
3  developer  verified
4  admin      moderator

在将角色分配给用户时，我希望强制要求父级角色是必需的，这意味着如果用户具有Admin角色，则他们还必须具有must和Verified。这意味着，如果用户失去了他们的版主角色，那么他们的Admin角色将被自动删除，如果Admin角色的父级被更改为Developer，那么用户将失去Admin角色，因为他们不再具有所需的父级。

到目前为止，我想到的唯一解决方案是在链接表上添加一个新列，该列引用了已经分配的父级，例如：

role-user link table
id  role       user_id  parent role-user link
1   verified   1        none
2   moderator  1        1
3   admin      1        2

这意味着如果我从User 1中删除了版主角色，他们将失去Admin角色，但如果我将Admin角色的父级更改为Developer，则不会从链接表中删除版主和Admin角色。有没有办法只用SQL / Foreign Key约束来实现这个功能？

Answer 1

假设您有一个表，其结构如下：

create table role_user (
    user_id int,
    role_id int,
    parent_role_id int,
    primary key (user_id, role_id)
);

表本身描述了层次结构关系。为了加强完整性，您可以在(user_id, role_id)列上的(user_id, parent_role_id)上使用自引用复合外键，该外键引用同一表中的另一行。完成此设置后，您只需启用选项on delete cascade即可获得您想要的行为：

create table role_user (
    user_id int,
    role_id int,
    parent_role_id int,
    primary key (user_id, role_id),
    foreign key (user_id, parent_role_id) 
        references role_user(user_id, role_id) 
        on delete cascade
);

-- sample data
select * from role_user;

user_id | role_id | parent_role_id
------: | ------: | -------------:
      1 |       1 |           
      1 |       2 |              1
      1 |       3 |              2


-- delete the top node in the hierarchy
delete from role_user where user_id = 1 and role_id = 1;

-- all children rows are deleted as well
select * from role_user;

user_id | role_id | parent_role_id
------: | ------: | -------------: