nutman nutman - 7 months ago 19
SQL Question

MySQL error - #1062 - Duplicate entry ' ' for key 2

I'm trying to insert a huge list of users to a MySQL database but everytime I try I get the error:

#1062 - Duplicate entry '' for key 2


It gives me this because the 2nd column is blank on quite a lot of the entries, so after it's inserted one blank entry in column 2, it won't let me add another. However, when I added most of the list yesterday I didn't get this error once even though a lot of the entries I added yesterday have a blank cell in column 2 as well. Whats going on?

This is the sql code to insert 1 entry. The rest follow the same format:

INSERT INTO users
(`id`,`title`,`firstname`,`lastname`,`company`,`address`,`city`,`county`
,`postcode`,`phone`,`mobile`,`category`,`email`,`password`,`userlevel`)
VALUES
('','','John','Doe','company','Streeet','city','county'
,'postcode','phone','','category','emial@email.co.uk','','');

Answer

In addition to Sabeen's answer:

The first column id is your primary key.
Don't insert '' into the primary key, but insert null instead.

INSERT INTO users
  (`id`,`title`,`firstname`,`lastname`,`company`,`address`,`city`,`county`
   ,`postcode`,`phone`,`mobile`,`category`,`email`,`password`,`userlevel`) 
VALUES     
  (null,'','John','Doe','company','Streeet','city','county'
  ,'postcode','phone','','category','emial@email.co.uk','','');

If it's an autoincrement key this will fix your problem.
If not make id an autoincrement key, and always insert null into it to trigger an autoincrement.

MySQL has a setting to autoincrement keys only on null insert or on both inserts of 0 and null. Don't count on this setting, because your code may break if you change server.
If you insert null your code will always work.

See: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Comments