如何基于一个表的递归属性更新多对多关系的链接表

Question

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

每个角色都是层次结构的一部分，Verified角色位于树的顶部。所有其他角色都使用表中的自引用关系从中继承。默认角色表如下所示，其中有一个从Verified继承的Moderator和Developer角色，以及一个从Moderator继承的Admin角色。

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

在为用户分配角色时，我想强制要求家长是必需的，这意味着如果用户具有管理员角色，他们还必须具有审核人和验证人。这意味着，如果用户失去了"主持人"角色，则其"管理员"角色应自动删除；如果"管理员"的父角色更改为"开发人员"，则用户应失去"管理员"，因为他们不再具有所需的父角色。

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

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

这意味着，如果我从用户1中删除了主持人角色，他们将失去管理员角色，但如果我将管理员角色的父级更改为开发人员，主持人和管理员角色将不会从链接表中删除。有没有一种方法可以在只使用SQL/外键约束的情况下实现此功能？

Answer 1

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

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

层次结构关系在表本身中进行了描述。为了增强完整性，可以在(user_id, parent_role_id)上使用自引用复合外键，该外键引用同一表中(user_id, 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
);

DB Fiddle上的演示：

-- 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
------: | ------: | -------------: