Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'

Asked 31/1, 2013 at 14:35 Answered 15/2, 2023 at 13:50

Solved mysql mysql-workbench error-code mysql-error-1062

I have a problem on this error message, when i try this:

INSERT INTO `PROGETTO`.`UFFICIO-INFORMAZIONI` (`ID`, `viale`, `num_civico`,  
`data_apertura`, `data_chiusura`, `orario_apertura`, `orario_chiusura`, 
`telefono`, `mail`, `web`, `Nome-paese`, `Comune`) 
VALUES (1, 'Viale Cogel ', '120', '2012-05-21', '2012-09-30', '08:00', '23:30',
'461801243', '[email protected]', 'Bolzanoturismo.it', 'Bolzano', 'BZ')

Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'

I haven't auto_increment data, PLEASE HELP me!

This is the table related, UFFICIO-INFORMAZIONI

CREATE  TABLE IF NOT EXISTS `PROGETTO`.`UFFICIO-INFORMAZIONI` (
  `ID` INT(11) NOT NULL ,
  `viale` VARCHAR(45) NULL ,
  `num_civico` VARCHAR(5) NULL ,
  `data_apertura` DATE NULL ,
  `data_chiusura` DATE NULL ,
  `orario_apertura` TIME NULL ,
  `orario_chiusura` TIME NULL ,
  `telefono` VARCHAR(15) NULL ,
  `mail` VARCHAR(100) NULL ,
  `web` VARCHAR(100) NULL ,
  `Nome-paese` VARCHAR(45) NOT NULL ,
  `Comune` CHAR(2) NOT NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `Nome_paese` (`Nome-paese` ASC) ,
  INDEX `Comune` (`Comune` ASC) ,
  CONSTRAINT `Nome_paese`
    FOREIGN KEY (`Nome-paese` )
    REFERENCES `PROGETTO`.`PAESE` (`Nome-paese` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `Comune`
    FOREIGN KEY (`Comune` )
    REFERENCES `PROGETTO`.`PAESE` (`Comune` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB

INSERT INTO

INSERT INTO `PROGETTO`.`UFFICIO-INFORMAZIONI` (`ID`, `viale`, `num_civico`, `data_apertura`, `data_chiusura`, `orario_apertura`, `orario_chiusura`, `telefono`, `mail`, `web`, `Nome-paese`, `Comune`) VALUES (1, 'Viale Cogel ', '120', '2012-05-21', '2012-09-30', '08:00', '23:30', '461801243', '[email protected]', 'Bolzanoturismo.it', 'Bolzano', 'BZ');
    INSERT INTO `PROGETTO`.`UFFICIO-INFORMAZIONI` (`ID`, `viale`, `num_civico`, `data_apertura`, `data_chiusura`, `orario_apertura`, `orario_chiusura`, `telefono`, `mail`, `web`, `Nome-paese`, `Comune`) VALUES (2, 'Via Olmo', '45', '2012-05-01', '2012-09-30', '08:00', '23:30', '393495169301', '[email protected]', 'Lechinformation.it', 'Lech', 'BZ');
    INSERT INTO `PROGETTO`.`UFFICIO-INFORMAZIONI` (`ID`, `viale`, `num_civico`, `data_apertura`, `data_chiusura`, `orario_apertura`, `orario_chiusura`, `telefono`, `mail`, `web`, `Nome-paese`, `Comune`) VALUES (3, 'Via Quercia', '37', '2012-05-11', '2012-09-30', '08:00', '23:30', '393381679321', '[email protected]', 'Trentoinformaiozni.it', 'Trento', 'TN');
    INSERT INTO `PROGETTO`.`UFFICIO-INFORMAZIONI` (`ID`, `viale`, `num_civico`, `data_apertura`, `data_chiusura`, `orario_apertura`, `orario_chiusura`, `telefono`, `mail`, `web`, `Nome-paese`, `Comune`) VALUES (4, 'Via Atene', '76', '2012-06-01', '2012-09-15', '08:00', '23:30', '39349361345', '[email protected]', 'SanMartino.it', 'San Martino di Castrozza', 'TN');
    INSERT INTO `PROGETTO`.`UFFICIO-INFORMAZIONI` (`ID`, `viale`, `num_civico`, `data_apertura`, `data_chiusura`, `orario_apertura`, `orario_chiusura`, `telefono`, `mail`, `web`, `Nome-paese`, `Comune`) VALUES (5, 'Via Salice', '45', '2012-05-01', '2012-09-20', '08:00', '23:30', NULL, '[email protected]', 'Pejoturismo.it', 'Pejo', 'TN');
    INSERT INTO `PROGETTO`.`UFFICIO-INFORMAZIONI` (`ID`, `viale`, `num_civico`, `data_apertura`, `data_chiusura`, `orario_apertura`, `orario_chiusura`, `telefono`, `mail`, `web`, `Nome-paese`, `Comune`) VALUES (6, 'Piazza Sempreverde', '34', '2012-05-15', '2012-09-15', '08:00', '23:30', '392516789', '[email protected]', 'Ortisei.it', 'Ortisei', 'BZ');

Popgun answered 31/1, 2013 at 14:35 Comment(5)

So, there's already a record in the table having ID=1. Since ID is the PRIMARY KEY, there cannot be multiple records with the same value. What would you like this operation to do? Ignore the new insertion? Update the existing record? Replace the existing record? – Carinacarinate 31/1, 2013 at 14:43

Are you sure the errors refer to the primary field of this table? Sould it refer to other ID from other tables? – Nape 31/1, 2013 at 14:50

0 14:55:20 INSERT INTO PROGETTO.UFFICIO-INFORMAZIONI (ID, viale, num_civico, data_apertura, data_chiusura, orario_apertura, orario_chiusura, telefono, mail, web, Nome-paese, Comune) VALUES (1, 'Viale Cogel ', '120', '2012-05-21', '2012-09-30', '08:00', '23:30', '461801243', '[email protected]', 'Bolzanoturismo.it', 'Bolzano', 'BZ') Error Code: 1062. Duplicate entry '1' for key 'PRIMARY' – Popgun 31/1, 2013 at 14:55

Probably you have an issue with foreign keys, I'm trying to replicate your problem with [sql fiddle] (sqlfiddle.com/#!2/c6fb9) but without success. Could you give as the complete db schema? – Nape 31/1, 2013 at 15:16

dl.dropbox.com/u/60796322/ultimateProgetto.sql – Popgun 31/1, 2013 at 15:28

The ~~main~~ reason why the error has been generated is because there is already an existing value of 1 for the column ID in which you define it as PRIMARY KEY (values are unique) in the table you are inserting.

Why not set the column ID as AUTO_INCREMENT?

CREATE  TABLE IF NOT EXISTS `PROGETTO`.`UFFICIO-INFORMAZIONI` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `viale` VARCHAR(45) NULL ,
   .....

and when you are inserting record, you can now skip the column ID

INSERT INTO `PROGETTO`.`UFFICIO-INFORMAZIONI` (`viale`, `num_civico`, ...) 
VALUES ('Viale Cogel ', '120', ...)

Adelaideadelaja answered 31/1, 2013 at 14:37 Comment(7)

i've just added ALL my INSERT INTO ; there are no duplicated PK.. that's because i can't understand what happened – Popgun 31/1, 2013 at 14:46

You might have run the inserts more than once. – Brutal 31/1, 2013 at 14:56

I've droped the schema and re-created for the 2 time... i always got the same error, as shown above. EDIT:: the auto_increment would be an option, but it would work also without it... – Popgun 31/1, 2013 at 15:1

Are there maybe inserts in the script you use to recreate the table? – Brutal 31/1, 2013 at 15:20

In the script, there is first the CREATE TABLE, then INSERT INTO. These are shown above, and there is not other query/record for that table (UFFICIO-INFORMAZIONI) – Popgun 31/1, 2013 at 15:30

EHM... SOLVED: while i was editing the mysql file i've done a cut&paste over another INSERT INTO.... so yes obviously there were at least 2 ID with the same number. Anyway know i know of this auto_increment way, thanks you! – Popgun 31/1, 2013 at 22:58

youtube.com/watch?v=UrYLYV7WSHM In this video "Cust_ID" contains "wilson44" two times... – Batsheva 17/8, 2017 at 16:33

If you are using PHPMyAdmin You can be solved this issue by doing this:

CAUTION: Don't use this solution if you want to maintain existing records in your table.

Step 1: Select database export method to custom:

enter image description here

Step 2: Please make sure to check truncate table before insert in data creation options:

enter image description here

Now you are able to import this database successfully.

Mesothelium answered 2/2, 2015 at 9:36 Comment(2)

This is one of good and time saving solution when using phpmyadmin or terminal to import database from another. – Sokol 30/5, 2018 at 7:6

When trying to create new indexes on a huge table, I've resorted to renaming, creating a new table from the old, adding my indexes and then inserting from the old table. Was getting duplicate primary key errors even on the empty new table. Truncating it before copying data fixed this. – Accelerant 26/11, 2018 at 19:58

If you have a new database and you make a fresh clean import, the problem may come from inserting data that contains a '0' incrementation and this would transform to '1' with AUTO_INCREMENT and cause this error.

My solution was to use in the sql import file.

SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';

Greiner answered 5/5, 2019 at 23:37 Comment(0)

If you are trying to populate a table from a SQL dump, make sure that the table listed in the "INSERT INTO" statements of the dump is the same one you are trying to populate. Opening "MyTable" and importing with a SQL dump will throw exactly that kind of error if the dump is trying to put entries into "MyOtherTable", which may already have entries.

Thermobarometer answered 29/8, 2013 at 14:24 Comment(0)

The problem is related with your file - you are trying to create a DB using a copy - at the top of your file you will find something like this:

CREATE DATABASE IF NOT EXISTS *THE_NAME_OF_YOUR_DB* DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; USE *THE_NAME_OF_YOUR_DB*;

and I'm sure that you already have a DB with this name - IN THE SAME SERVER - please check. Just change the name OR ERASE THIS LINE!

Seventeen answered 25/8, 2013 at 9:24 Comment(0)

Also check your triggers.

Encountered this with a history table trigger which tried to insert the main table id into the history table id instead of the correct hist-table.source_id column.

The update statement did not touch the id column at all so took some time to find:

UPDATE source_table SET status = 0;

The trigger tried to do something similar to this:

FOR EACH ROW
BEGIN
    INSERT INTO `history_table` (`action`,`id`,`status`,`time_created`)
    VALUES('update', NEW.id, NEW.status, NEW.time_created);
END;

Was corrected to something like this:

FOR EACH ROW
BEGIN
    INSERT INTO `history_table` (`action`,`source_id`,`status`,`time_created`)
    VALUES('update', NEW.id, NEW.status, NEW.time_created);
END;

Broccoli answered 23/1, 2019 at 16:56 Comment(0)

When I get this kind of error I had to update the data type by a notch. For Example, if I have it as "tiny int" change it to "small int" ~ Nita

Fleeman answered 3/10, 2013 at 15:26 Comment(0)

I just encountered the same issue but here it seemed to come from the fact that I declared the ID-column to be UNsigned and that in combination with an ID-value of '0' (zero) caused the import to fail...

So by changing the value of every ID (PK-column) that I'd declared '0' and every corresponding FK to the new value, my issue was solved.

Reverberatory answered 13/2, 2015 at 14:3 Comment(0)

-1

Using MySQL Workbench Import/Export

I got the same error as op when using MySQL Workbench Data Export. I was manually moving specific tables from one local device to another; both devices having MySQLWB.

FAILED: data-only export

On import, I got erors like: ERROR 1062 (23000) at line 83: Duplicate entry '' for key...

WORKED: structure-and-data export

No other changes; everything else stayed the same.

Lianaliane answered 15/2, 2023 at 13:50 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Using MySQL Workbench Import/Export

Recommended topics

Hot tags