MySQL – Unique Constraint on 3 Columns with Specific Value Allowing Duplicates

enumindexMySQLunique-constraintusers

I have a 'Users' table with columns user_email, user_company_id and user_status. The user_status column is an enum with values '1' or '0' which represents the users being either active or inactive. Is there a way to apply a unique constraint to these 3 columns such that it only allows one unique, active user email for a specific company but any number of duplicate entires for inactive emails?

E.g.: Consider a 'Users' table with the following entries

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

I shouldn't be able to add an existing, active email for a specfic company twice; the following should fail:

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

If I update the status of one of the active users to '0' (inactive), I should be able to insert the same email again since the previous email status is inactive. The following should succeed:

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

Also, the constraint should allow duplicate entries for inactive user emails. This should also succeed:

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

Best Answer

As i saig in the comment yoi have t make a BEFORE INSERT trigger

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          

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

User already activated

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

db<>fiddle here

Related Question