时间戳

首页 > 解决方案 > 如何插入具有多对多关系的表中

sql - 如何插入具有多对多关系的表中

问题描述

â我有三张桌子。权限、角色和引用这两个表的第三个表role_permissions。我想在角色和角色权限表中插入一条记录。

如何才能做到这一点？

权限

+---------------+-----------+
| permission_id |   name    |
+---------------+-----------+
|      1        | role_edit |
+---------------+-----------+

角色

+---------------+-----------+
|    role_id    |   name    |
+---------------+-----------+
|      1        |    admin  |
+---------------+-----------+

角色权限

+---------------+-----------------+
|   role_id     |  permission_id  |
+---------------+-----------------+
|      1        |         1       |
+---------------+-----------------+

标签： sqldatabasepostgresqlmany-to-many

解决方案

正如其他人指出的那样，假设适当的主键和外键就位，您必须先创建rolesandpermissions才能创建 role_permissions。不幸的是，Postgres 没有提供直接的方法来获取表的 LAST ID，而且最后一个角色或权限通常可能是您需要的。您需要对每个名称列设置唯一约束。然后创建 role_permissions 你可以自信地id从name你想要组合的地方获得适当的权限。您使用这些名称连接两个表。

insert into role_permissions(role_id, permission_id)
      select role_id, permission_id 
        from roles       r
        join permissions p  
          on (    r.name = 'group1' 
              and p.name = 'read all'
             );

您还可以创建一个过程来处理整个过程： insert roles， insertpermissions和 insertrole_permissions只给出您想要组合的名称。

create or replace 
procedure build_role_permissions( role_name_in text  
                                , permission_name_in text 
                                ) 
  language sql
as $$
    insert into roles(name) 
      values(role_name_in)
    on conflict (name) 
        do nothing; 
        
    insert into permissions(name) 
      values(permission_name_in)
    on conflict (name) 
        do nothing;  
        
    insert into role_permissions(role_id, permission_id)
      select role_id, permission_id 
        from roles       r
        join permissions p  
          on (    r.name = role_name_in
              and p.name = permission_name_in
             ) 
    on conflict (role_id, permission_id) 
        do nothing;
$$;

见演示。

推荐阅读