使用一个SQL命令将数据添加到多对多关系

admin

使用一个SQL命令将数据添加到多对多关系

sql

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

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

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

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），则工作正常。但是我不知道，如果Joe或admin是来自数据库的任务，如何实现“添加-如果缺少-然后-返回-
id”的行为。

示例（缺少用户和角色）：

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

阅读 204

2021-07-01

共1个答案

admin

您可以从表创建视图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它可以是这样的：

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 OR IGNORE防止将重复值插入到所有三个表中的用法。以下是通过视图插入值的方法：

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)产生以下输出：

user_name	role_name
Joe	admin
Joe	system
Alice	admin
Bob	system

2021-07-01