對 3 個不同列的唯一約束，但第 3 列中的特定值允許重複的行條目

我有一個“使用者”表，其中包含user_email和user_company_id列user_status。該user_status列是一個列舉，其值為“1”或“0”，表示使用者處於活動狀態或非活動狀態。有沒有辦法對這 3 列應用唯一約束，以便它只允許特定公司的一個 唯一的、活動的使用者電子郵件，但**不活動的電子郵件有任意數量的重複整體？

例如：考慮一個包含以下條目的“使用者”表

CREATE TABLE users(
 user_id BIGINT(10) PRIMARY KEY AUTO_INCREMENT, 
 user_email VARCHAR(255) NOT NULL, 
 user_companyid BIGINT(10) NOT NULL, 
 user_status enum('1', '0'))

INSERT INTO users(user_id, user_email, user_companyid, user_status) 
 VALUES (1,'test1@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
 VALUES (2,'test2@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
 VALUES (3,'test1@gmail.com','777','1');

SELECT * FROM users;


user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
     1 | test1@gmail.com |            555 | 1          
     2 | test2@gmail.com |            555 | 1          
     3 | test1@gmail.com |            777 | 1           

我不應該兩次為特定公司添加現有的、活動的電子郵件；以下應該失敗：

INSERT INTO users(user_id, user_email, user_companyid, user_status) 
VALUES (4,'test1@gmail.com','555','1'); 

如果我將其中一個活動使用者的狀態更新為“0”（非活動），我應該能夠再次插入相同的電子郵件，因為之前的電子郵件狀態是非活動的。以下應該成功：

UPDATE users SET user_status = '0' WHERE user_id = 1;

INSERT INTO users(user_id, user_email, user_companyid, user_status) 
 VALUES (4,'test1@gmail.com','555','1');

user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
     1 | test1@gmail.com |            555 | 0          
     2 | test2@gmail.com |            555 | 1          
     3 | test1@gmail.com |            777 | 1          
     4 | test1@gmail.com |            555 | 1          

此外，該約束應允許非活動使用者電子郵件的重複條目。這也應該成功：

UPDATE users SET user_status = '0' WHERE user_id = 4;

SELECT * FROM users;

user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
     1 | test1@gmail.com |            555 | 0          
     2 | test2@gmail.com |            555 | 1          
     3 | test1@gmail.com |            777 | 1          
     4 | test1@gmail.com |            555 | 0

正如我在評論中所說，你沒有在插入之前觸發

CREATE TABLE users(
  user_id BIGINT(10) PRIMARY KEY AUTO_INCREMENT, 
  user_email VARCHAR(255) NOT NULL, 
  user_companyid BIGINT(10) NOT NULL, 
  user_status enum('1', '0'))

✓

INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (1,'test1@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (2,'test2@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (3,'test1@gmail.com','777','1');

✓

✓

✓

SELECT * FROM users;

使用者 ID | 使用者郵箱 | user_companyid | 使用者狀態
------: | :-------------- | -------------: | :----------
 1 | test1@gmail.com | 555 | 1 
 2 | test2@gmail.com | 555 | 1 
 3 | test1@gmail.com | 777 | 1 

CREATE TRIGGER users_before_insert
BEFORE INSERT
   ON users FOR EACH ROW

BEGIN

   DECLARE vUser varchar(50);

   -- Find username of person performing INSERT into table
   IF EXISTS(SELECT 1 
            FROM users 
            WHERE 
             user_email = NEW.user_email
             AND user_companyid = NEW.user_companyid
             AND user_status = 1) THEN
     signal sqlstate '45000' 
     SET MESSAGE_TEXT = 'User already activated';

  END IF;

END; 

✓

INSERT INTO users( user_email, user_companyid, user_status) 
  VALUES ('test1@gmail.com','555','1');

使用者已啟動

SELECT * FROM users;

使用者 ID | 使用者郵箱 | user_companyid | 使用者狀態
------: | :-------------- | -------------: | :----------
 1 | test1@gmail.com | 555 | 1 
 2 | test2@gmail.com | 555 | 1 
 3 | test1@gmail.com | 777 | 1 

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/264799