Unique constraint on 3 different columns but a specific value in 3rd column allows duplicate row entries

Question

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,'[email protected]','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (2,'[email protected]','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (3,'[email protected]','777','1');

SELECT * FROM users;


user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
      1 | [email protected] |            555 | 1          
      2 | [email protected] |            555 | 1          
      3 | [email protected] |            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,'[email protected]','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,'[email protected]','555','1');

user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
      1 | [email protected] |            555 | 0          
      2 | [email protected] |            555 | 1          
      3 | [email protected] |            777 | 1          
      4 | [email protected] |            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 | [email protected] |            555 | 0          
      2 | [email protected] |            555 | 1          
      3 | [email protected] |            777 | 1          
      4 | [email protected] |            555 | 0

Create a fiddle with the table and some valid rows and a row that should violate the constraint — Lennart - Slava Ukraini, Commented Apr 11, 2020 at 16:45
CHECK maybe doesn 't do it : you can always write a BEFORE INSERT trigger to check and denoy inserts — nbk, Commented Apr 11, 2020 at 16:56

Community · Accepted Answer · 2020-06-15 09:05:18Z

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,'[email protected]','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (2,'[email protected]','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (3,'[email protected]','777','1');

✓

✓

✓

SELECT * FROM users;

user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
      1 | [email protected] |            555 | 1          
      2 | [email protected] |            555 | 1          
      3 | [email protected] |            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 ('[email protected]','555','1');

User already activated

SELECT * FROM users;

user_id | user_email      | user_companyid | user_status
------: | :-------------- | -------------: | :----------
      1 | [email protected] |            555 | 1          
      2 | [email protected] |            555 | 1          
      3 | [email protected] |            777 | 1

db<>fiddle here

The new row may have user_status=0, in which case it would be ok to insert. — Lennart - Slava Ukraini, Commented Apr 12, 2020 at 5:16
But will this affect the performance of the queries if I'm bulk-inserting say 500-1000 users at once? — Jay, Commented Apr 12, 2020 at 17:55
Make a combined index onuser_email, user_companyid and user_status and will not takle too much performace, but as far as i can see it is the only way and with the combned index it is quick — nbk, Commented Apr 12, 2020 at 18:42
Thanks, will try this out and mark it as the accepted answer if no other solutions pop up. Also, in case of a bulk-insert, does this halt the query execution completely if one of the inserts is a duplicate entry? — Jay, Commented Apr 12, 2020 at 19:27

Lennart - Slava Ukraini · Accepted Answer · 2023-01-12 13:13:06Z

This did not work because auto_increment columns cannot be referenced by a generated column, but I will add it anyhow since it demonstrates a technique that can be useful. The idea is to use a generated column, that when user_status = 0 maps to something guaranteed unique (primary key) and otherwise maps to a constant. Then this column can be included in a UNIQUE constraint together with the columns that should be unique under condition:

CREATE TABLE users
( user_id BIGINT PRIMARY KEY  -- auto_increment had to be removed
, user_email VARCHAR(255) NOT NULL
, user_companyid BIGINT NOT NULL
, user_status enum('1', '0')
, gencol BIGINT GENERATED ALWAYS as 
    ( CASE WHEN user_status = 1
           THEN -1
           ELSE user_id
      END
    ) NOT NULL
);

ALTER TABLE users ADD CONSTRAINT ak1
    UNIQUE (user_email, user_companyid, gencol);
    
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (1,'[email protected]','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (2,'[email protected]','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (3,'[email protected]','777','1');

-- Fails    
-- INSERT INTO users(user_id, user_email, user_companyid, user_status) 
-- VALUES (4,'[email protected]','555','1'); 
 
UPDATE users SET user_status = '0' WHERE user_id = 1;

-- Succeeds
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
VALUES (4,'[email protected]','555','1');

EDIT: as suggested by @ypercubeᵀᴹ in comment. By allowing null in the generated column, we can use null instead of user_id (which is auto incremented)

CREATE TABLE users
( user_id BIGINT PRIMARY KEY AUTO_INCREMENT
, user_email VARCHAR(255) NOT NULL
, user_companyid BIGINT NOT NULL
, user_status enum('1', '0')
, gencol BIGINT GENERATED ALWAYS as 
    ( CASE WHEN user_status = 1
           THEN -1
           ELSE NULL
      END
    ) 
);

ALTER TABLE users ADD CONSTRAINT ak1
    UNIQUE (user_email, user_companyid, gencol);


INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (1,'[email protected]','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (2,'[email protected]','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
  VALUES (3,'[email protected]','777','1');

-- Fails    
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
VALUES (4,'[email protected]','555','1'); 

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

-- Succeeds
INSERT INTO users(user_id, user_email, user_companyid, user_status) 
VALUES (4,'[email protected]','555','1');

Fiddle

Thanks for your answer Lennart. Although this solution does work, I am not authorized to remove the auto_increment attribute from the table or alter it to add a new generated column. Is there any other solution that achieves the same result by just adding specific constraints? — Jay, Commented Apr 12, 2020 at 18:22
Also, I'll be more clear with the actual issue I'm facing. I have a bulk-upload function in my application which as the name suggests, inserts users in bulk from a csv. I can actually make do without having a unique constraint on the table for the most part as I'm validating the data before inserting them. But the issue is, if two parallel requests with same set of users come in at the EXACT same time, the function ends up adding duplicate entries. It's not a problem even if there is a delay of one second between the requests but parallel requests are definitely an issue. — Jay, Commented Apr 12, 2020 at 18:42
@Jay, I added it despite that it does not work in case it can be used in another situation. I tried with something else beeing unique, like uuid(), but that is not allowed in a generated column. Neither is there sequences that can be used. Ill see if something else shows up — Lennart - Slava Ukraini, Commented Apr 12, 2020 at 21:47
You can also use THEN -1 ELSE NULL instead of THEN -1 ELSE user_id in the column definition. Or THEN 1 ELSE NULL — ypercubeᵀᴹ, Commented Jan 12, 2023 at 11:55
@Lennart which will avoid the problem with the auto_increment. ^^ Feel free to use it in your answer: dbfiddle.uk/zPxUeLJv — ypercubeᵀᴹ, Commented Jan 12, 2023 at 12:02

Chakreshwar Sharma · Accepted Answer · 2023-01-12 05:02:44Z

Adding few more things in accepted answer. Dont forget to put delimiters in the trigger otherwise it will give missing ; error or You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1

DELIMITER $$
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; 
$$
DELIMITER ;

You needs to have SUPER privilege to run this otherwise you will get You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

To set super privileges checkout this

Stack Exchange Network

Unique constraint on 3 different columns but a specific value in 3rd column allows duplicate row entries

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
index
users
unique-constraint
enum
or ask your own question.

Hot Network Questions

Unique constraint on 3 different columns but a specific value in 3rd column allows duplicate row entries

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlindexusersunique-constraintenum or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
index
users
unique-constraint
enum
or ask your own question.