1

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
  • 1
    Create a fiddle with the table and some valid rows and a row that should violate the constraint – Lennart Apr 11 at 16:45
  • dbfiddle.uk/?rdbms=mysql_8.0 – Lennart Apr 11 at 16:48
  • CHECK maybe doesn 't do it : you can always write a BEFORE INSERT trigger to check and denoy inserts – nbk Apr 11 at 16:56
  • Specify your MySQL version - it is critical for this task. – Akina Apr 11 at 16:56
  • @nbk CHECK validtion do NOT allow such type of check. – Akina Apr 11 at 16:57
0

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

| improve this answer | |
  • The new row may have user_status=0, in which case it would be ok to insert. – Lennart Apr 12 at 5:16
  • But will this affect the performance of the queries if I'm bulk-inserting say 500-1000 users at once? – Jay Apr 12 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 Apr 12 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 Apr 12 at 19:27
0

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

-- Fails    
-- INSERT INTO users(user_id, user_email, user_companyid, user_status) 
-- VALUES (4,'test1@gmail.com','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,'test1@gmail.com','555','1'); 
| improve this answer | |
  • 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 Apr 12 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 Apr 12 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 Apr 12 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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