Duplicate entry '' for key 'users_email_unique' empty field

Question

I have two types of users - real and fake. Fake users are employees, that don't use the system. Real users use their email address to login. So my users migration has $table->string('email')->unique();.

The problem is that fake users may not have an email address. I can add first fake user no problem, but the second one generates error SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'users_email_unique'.

What should I do?

Answer 1

Sounds like users_email_unique is your primary key. This means that when you insert your first fake user with a blank email address, the blank entry is counted as a unique entry. When your second blank entry is entered it violates entity integrity (duplicate primary keys).

users_email_unique    unique?
_________________     _______
[email protected]       yes
blank                 yes
blank                 no

If you want multiple blank entries you could allow users_email_unique to be null, however, a primary key column cannot contain null values:

users_email_unique   unique?
__________________   ________
[email protected]      yes
NULL                 yes
NULL                 yes

If you are using users_email_unique as your primary key then, as pointed out in the comments, you may need to either:

• generate a random unique email temporarily for 'fake' users
• reconsider your primary key for your table (perhaps some form of unique ID?)
• perhaps split into two tables, one for 'real' users and one for 'fake'