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');
This question is related to
mysql
mysql-workbench
error-code
mysql-error-1062
The answer is
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.
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.
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;
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:
Step 2: Please make sure to check truncate table before insert in data creation options:
Now you are able to import this database successfully.
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
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';
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!
Source: Stackoverflow.com