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

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