3

I have seen this error with people running php scripts before but this is happending to me in phpmyadmin ??

Error
SQL query:

UPDATE  `cl56-goldeng`.`users` SET `email` =  '' WHERE  `users`.`id` =118

MySQL said: Documentation

#1062 - Duplicate entry '' for key 'email'

It works fine if I give the field another value, but if I clear the field and press enter I get the above error.

The table itself looks like this : enter image description here

Iain Simpson
  • 441
  • 4
  • 13
  • 29
  • 1
    You have a unique key constraint on the email field in your users table. Unique keys only allow 1 of a certain thing to exist in the table. In your case, there is already a user record with an empty string as their email. Try setting it to NULL instead of empty. – Mike Elofson Nov 12 '15 at 14:40

3 Answers3

5

On your table cl56-goldeng.users, the field email was specified on creation to not allow more than 1 of the same value to be allowed into it. This is done using the UNIQUE identifier on table creation in MySQL. You can see more on the UNIQUE identifier at this link.

You have 2 options that you could go about doing.

  • First would be to remove the unique constraint on the email field. This entirely depends on your logic in your code, but seeing as emails should almost always be unique, this is not suggested.

You can drop a unique key by running the command: alter table [table-name] drop index [unique-key-index-name];

  • Second, would be to use NULL instead of an empty string. My assumption is that you are setting an empty string when the users email does not exist. In this scenario, it would be better to use NULL, and then check for that when retrieving data from the database.

You can insert a NULL value by using the NULL identifier in your MySQL statement, like such:

INSERT INTO users (firstName,lastName,email)
  VALUES ('Bob','Ross',NULL);

And then check for a NULL value in whatever language you are accessing this data from.

Mike Elofson
  • 2,017
  • 1
  • 10
  • 16
  • Thanks, I am currently using : – Iain Simpson Nov 12 '15 at 14:47
  • if(isset($_POST['email'])){$email = $_POST['email'];}else{$email = NULL;} – Iain Simpson Nov 12 '15 at 14:47
  • It more pertains to the SQL query that you are executing to insert the value. If you are inserting the `NULL` as a `String` variable (between quotes), it may be interpreted as an empty string in php (been awhile since I have used php, full disclosure). You should run a check in your query, `email === null ? NULL : ''.$variable.''` – Mike Elofson Nov 12 '15 at 14:51
  • Thanks, this seems to have sorted it :) – Iain Simpson Nov 12 '15 at 15:16
2

You have a unique constraint on your email field. Either rethink your logic or drop the unique constraint.

Community
  • 1
  • 1
ʰᵈˑ
  • 11,279
  • 3
  • 26
  • 49
  • How do you get rid of the unique constraint without dropping the field, as cant see anything to remove it in phpmyadmin ? – Iain Simpson Nov 12 '15 at 14:43
  • Run `show create table users`. This will dump your table which will have your unique constraint. You can then `ALTER TABLE users DROP CONSTRAINT ` – ʰᵈˑ Nov 12 '15 at 14:47
1

Thats because you may have declare the email as unique key, and once you enter one row of empty email, it wont except another empty email

Waqar Haider
  • 929
  • 10
  • 33