How to insert into table that has many-to-many relationship-CodePudding

вI have three tables. permissions, roles and a third table that references these two tables role_permissions. I want to insert a record into roles and role_permissions table.

How can this be done?

permissions

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

roles

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

role_permissions

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

CodePudding user response：

At first you insert in roles table, then you insert in role_permissions where role_id - is a value returned from previous insert, for mysql, for example, is a LAST_INSERT_ID() function to be used, and permission_id is a value from permissions. Like this (for MySql):

INSERT INTO roles(name) VALUES('<some_role>');
INSERT INTO role_permissions(role_id,permission_id) VALUES(SELECT 
LAST_INSERT_ID(),1);

CodePudding user response：

As others indicate you must create the roles and permissions before you can create role_permissions, assuming appropriate primary and foreign keys are in place. Unfortunately, Postgres does not provide a direct method to obtain the LAST ID for a table, further the last role or permission may often be the one you need. What you need is to put a unique constraint on each of the name columns. Then to create the the role_permissions you can confidently get the appropriate id from the name you wish to combine. You join the two tables using those names.

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'
             );

You can also create a procedure that will handle the complete process: insert roles, insert permissions and insert role_permissions given just the names you want to combine.

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;
$$;

See demo.