mysql - 添加 mysql 约束或触发器

Question

我有一个关于 MySQL 中的触发器和唯一约束的快速问题。我将举一个简单的例子。

我有两个表，“帐户”和“用户”。这两个表之间的关系是 1(帐户)到许多(用户)。因此，如果您打开一个具有唯一 ID 的帐户表，您会遇到该帐户的多个用户。用户表中有一个名为“user_type”的列。 'user_type' 可以是 'admin'、'guest'、'trial'。 “帐户”必须有一个将“admin”作为“user_type”的用户。每个帐户只能有 1 个“管理员”。

那么，有没有办法添加唯一约束，使任何帐户只能拥有 1 个“admin”user_type？或者它必须是一个触发器，因为它是 MySQL？

谢谢!

Answer 1

确保每个帐户只有一个管理员用户的最简单方法是拥有指向作为管理员的用户行的外键。

CREATE TABLE Users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  account_id INT
);

CREATE TABLE Account (
  account_id INT AUTO_INCREMENT PRIMARY KEY,
  admin_user_id INT NOT NULL
);

ALTER TABLE Account ADD FOREIGN KEY (admin_user_id) 
  REFERENCES Users (user_id);

ALTER TABLE Users ADD FOREIGN KEY (account_id) 
  REFERENCES Account(account_id);

INSERT INTO Users () VALUES ();
SET @uid = LAST_INSERT_ID();
INSERT INTO Account (admin_user_id) VALUES (@uid);
UPDATE Users SET account_id = LAST_INSERT_ID() WHERE user_id = @uid;

确保每个帐户的用户仅限于一个管理员，我认为其他类型的多个用户有点棘手。但是通过 MySQL 5.7 中的虚拟列，我们可以做到。

ALTER TABLE Users 
  ADD COLUMN user_type ENUM('admin', 'guest', 'trial') NOT NULL DEFAULT 'guest',
  ADD COLUMN is_admin TINYINT(1) AS (IF(user_type='admin',1,NULL)) STORED,
  ADD UNIQUE KEY (account_id, is_admin);

SELECT * FROM Users;
+---------+------------+-----------+----------+
| user_id | account_id | user_type | is_admin |
+---------+------------+-----------+----------+
|       1 |          1 | guest     |     NULL |
+---------+------------+-----------+----------+

INSERT INTO Users (account_id, user_type) VALUES (1, 'admin');

SELECT * FROM Users;
+---------+------------+-----------+----------+
| user_id | account_id | user_type | is_admin |
+---------+------------+-----------+----------+
|       1 |          1 | guest     |     NULL |
|       2 |          1 | admin     |        1 |
+---------+------------+-----------+----------+

INSERT INTO Users (account_id, user_type) VALUES (1, 'admin');
ERROR 1062 (23000): Duplicate entry '1-1' for key 'account_id'

虚拟列上的 UNIQUE KEY 忽略 NULL。因此可以存在任意数量的 guest 或试用用户，并且他们都在虚拟列中由 NULL 表示。但是每个帐户可以存在一个管理员用户，该虚拟列中的值为 1。