CODEWITHSUNDEEP
mysqlmysql-5.7
Morteza Pouretemadi
Morteza Pouretemadi

Reputation: 648

MySQL - Unique Confirmed Emails

I have a project on Yii2 and I want to fix a bug in registration form. This is my user_profile table:

id | email | email_confirmed
____________________________
1  | [email protected] | 0

I have a unique index on email column and when user confirm his/her email address, email_confirmed column will be 1.

Now here is the problem: If user 1 do not confirm his/her email, no one else can register that email.

One solution came to my mind is removing unique index and handle it by PHP. But it's not suit me for best. I want to know is there any better solution with MySql?

Upvotes: 3

Views: 89

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175944

You could use function index to handle partial indexes(MySQL 8.0.13 and newer):

CREATE TABLE t
AS    SELECT 1 id, '[email protected]' email,  0 AS email_confirmed
UNION SELECT 2 id, '[email protected]' email,  1 AS email_confirmed;

And index:

CREATE UNIQUE INDEX uq_t ON t((CASE WHEN email_confirmed = 1 THEN email END));

Trying to insert another occurence of email that is confirmed:

INSERT INTO t(id, email, email_confirmed) VALUES (3, '[email protected]', 1);
-- Duplicate entry '[email protected]' for key 'uq_t'

db<>fiddle demo

Upvotes: 2

Related Questions