使用一个SQL命令将数据添加到多对多关系 [英] Add data to many-to-many relation with one SQL command

问题描述

我对SQL数据库有基本的了解，但我可能忽略了某些内容，但是我无法弄清楚以下问题:存在多对多关系(例如:users-user_roles-角色).是否可以使用一个SQL命令(原子操作)向(新)用户添加(新)角色?目前，我使用Sqlite.

I have a basic understanding of SQL databases and I might overlooked something, but I cannot figure out the following problem: there is a many-to-many relationship (for example: users - user_roles - roles). Is it possible to add (new) role to a (new) user with one SQL command (atomic operation)? Currently I use Sqlite.

我知道 SELECT last_insert_rowid(); 命令，并通过此命令和一些SQL命令可以实现所需的功能.但是我想将其合并到一个命令中(因此服务器(在这种情况下为Sqlite)可以优化查询等).我不知道它是如何在现实生活中完成的(一个命令与多个一对一的事务)，这就是这个问题的根本原因.

I am aware of the SELECT last_insert_rowid(); command and with this and several SQL commands I can achieve what I want. But I want to incorporate it into one command (so the server, in this case Sqlite, can optimize the query, etc.). I have no idea, how it is done in real life (one command vs. several one in one transaction), that´s the root cause of this question.

到目前为止，这是我能够做到的:

So far this is what I was able to do:

pragma foreign_keys = on;

CREATE TABLE users (
  user_id integer primary key autoincrement,
  user_name text not null unique
);

CREATE TABLE roles (
  role_id integer primary key autoincrement,
  role_name text not null unique
);

CREATE TABLE user_roles (
  user_id integer not null,
  role_id integer not null,
  foreign key (user_id) references users(user_id),
  foreign key (role_id) references roles(role_id),
  primary key (user_id, role_id)
);

insert into users (user_name) values ('Joe');
insert into roles (role_name) values ('admin');

insert into user_roles (user_id, role_id) values (
  (select user_id from users where user_name = 'Joe'),
  (select role_id from roles where role_name = 'admin')
);

如果用户和角色都存在(Joe 和 admin)，那么它工作正常.但是我不能弄清楚如何实现添加如果缺少然后返回ID".如果Joe或admin是来自数据库的任务，则该行为.

If both user and role exists (Joe and admin), then it works fine. But I cannot figure out, how to achieve "add-if-missing-then-return-id" behavior if Joe or admin is mission from database.

示例(缺少用户和角色):

Example (both user and role are missing):

insert into user_roles (user_id, role_id) values (
  (select user_id from users where user_name = 'Bill'),
  (select role_id from roles where role_name = 'user')
);

结果:

Execution finished with errors.
Result: NOT NULL constraint failed: user_roles.user_id

推荐答案

您可以创建视图 user_roles 表:

CREATE VIEW user_roles_view AS
  SELECT
    U.user_name, R.role_name
  FROM user_roles AS UR
    INNER JOIN users AS U ON u.user_id = UR.user_id
    INNER JOIN roles AS R ON r.role_id = UR.role_id;

SQLite中的视图是只读的，除非您在其上创建 INSTEAD OF触发器.这样，您可以指定使用 INSERT ， UPDATE 或 DELETE 语句修改视图时执行的命令或命令序列.对于 INSERT ，它可能像这样:

Views in SQLite are read-only unless you create an INSTEAD OF trigger on it. This way you can specify a command or sequence of commands that are executed when the view is modified using INSERT, UPDATE or DELETE statement. For INSERT it could go like this:

CREATE TRIGGER user_roles_view_insert INSTEAD OF INSERT ON user_roles_view
BEGIN
  INSERT OR IGNORE INTO users (user_name) VALUES (NEW.user_name);
  INSERT OR IGNORE INTO roles (role_name) VALUES (NEW.role_name);
  INSERT OR IGNORE INTO user_roles (user_id, role_id) VALUES (
    (SELECT user_id FROM users WHERE user_name = NEW.user_name),
    (SELECT role_id FROM roles WHERE role_name = NEW.role_name)
  );
END;

请注意使用 INSERT或IGNORE 来防止在三个表中都插入重复值.通过视图插入值的方法如下:

Note the usage of INSERT OR IGNORE to prevent inserting duplicate values into all of the three tables. Here's how you would insert values via the view:

INSERT INTO user_roles_view VALUES ('Joe', 'admin');
-- The above statement creates:
--   a row (1, 'Joe') in table users,
--   a row (1, 'admin) in table roles,
--   a row (1, 1) in table user_roles.

INSERT INTO user_roles_view VALUES ('Joe', 'admin');
-- The above statement doesn't add any additional records, because all appropriate records
-- already exist.

INSERT INTO user_roles_view VALUES ('Joe', 'system');
-- The above statement creates:
--   a row (2, 'system') in table roles,
--   a row (1, 2) in table user_roles.

INSERT INTO user_roles_view VALUES ('Alice', 'admin'), ('Bob', 'system');
-- The above statement creates:
--   a row (2, 'Alice') in table users,
--   a row (3, 'Bob') in table users,
--   a row (2, 1) in table user_roles,
--   a row (3, 2) in table user_roles

以上所有语句从user_roles_view( SELECT * FROM user_roles_view )产生以下输出:

All of the above statements produce the following output from user_roles_view (SELECT * FROM user_roles_view):

<身体>

用户名	role_name
乔	admin
乔	系统
爱丽丝	admin
鲍勃	系统

这篇关于使用一个SQL命令将数据添加到多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！