3

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?

Edmund Sulzanok
  • 1,883
  • 3
  • 20
  • 39
  • 3
    Insert fake users as NULL,this work because mysql treats NULL values as unqiue – Mihai Aug 13 '15 at 09:24
  • why dont u make two tables like `tblrealUsers` and `tblfakeUsers`? – Prashant Tapase Aug 13 '15 at 09:26
  • The email field cannot be unique if you are going to have more than one user with the "" email. Yo have to delete the restriction or use any "tricky" solution for example: "[email protected]" being XX the user ID. – Enrique Muñoz Rodas Aug 13 '15 at 09:26
  • @Mihai really? but how it works? – Prashant Tapase Aug 13 '15 at 09:28
  • If you use an orm you should want to be database agnostic and using null is not a good idea for many reason, first is that only mysql is that dumb. Take the answer from Enrique that ninjed me xD – Luca Bruzzone Aug 13 '15 at 09:28
  • @LucaBruzzone So how using empty string for a few employees is smart but NULLs is dumb? – Mihai Aug 13 '15 at 09:30
  • I never suggested to use an empty string and I said that mysql id dumb to treat null as unique (because you can make a mess in the indexes) In my answer I was suggesting to remove the rows or use a fake email (which are unique and can be sharded eventually and if someone wants to change the db can do it without pain) – Luca Bruzzone Aug 13 '15 at 09:46

3 Answers3

3

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'
Scott
  • 1,863
  • 2
  • 24
  • 43
  • I went for `$table->dropUnique('email_users_unique');`, but I'd rather have set email to `NULL` if user is fake and his email is not provided. – Edmund Sulzanok Aug 13 '15 at 11:19
  • @EdmundSulzanok If I try that I get this error: ` SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'email_u sers_unique'; check that column/key exists (SQL: alter table `users` drop i ndex `email_users_unique`)` – Hillcow Nov 11 '19 at 20:34
  • @Hillcow could it mean, that your email column is already not unique? – Edmund Sulzanok Nov 12 '19 at 07:34
0

You can act in 2 ways:

  • delete the line where the email is null (if there are user that aren't fake this process will kill their accounts)
  • give a random email with a custom domain like [email protected] and then you can control in the class when you log in if the mail matches the fake mail and ask to the user to insert a real mail if he want to continue using the website

(eventually you can have the 2nd solution for a while and then pass to the 1st)

Luca Bruzzone
  • 561
  • 4
  • 15
0

enter image description here

For any other person who might be looking for a solution in 2020

Look for the this indexes on the users table in phpmyadmin.

Deleting the user_up_number_unique index might solve the problem

kamasuPaul
  • 173
  • 1
  • 9