mysql 错误代码：1062,关键字'PRIMARY'的条目'1'重复

kulphzqa 于 2022-12-10 发布在 Mysql

关注(0)|答案(9)|浏览(180)

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', 'informazioni@bolzano.it', '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', 'informazioni@bolzano.it', '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', 'informazioni@lech.it', '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', 'info@trento.it', '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', 'info@sanmartinodicastrozza.it', '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, 'info@pejo.it', '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', 'info@ortisei.it', 'Ortisei.it', 'Ortisei', 'BZ');

mysql

来源：https://stackoverflow.com/questions/14628269/error-code-1062-duplicate-entry-1-for-key-primary

9条答案

按热度按时间

2ledvvac1#

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', ...)

赞(0）回复(0）举报 2022-12-10

qeeaahzv2#

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.

赞(0）回复(0）举报 2022-12-10

6za6bjd03#

如果您有一个新的数据库，并且进行了全新的导入，则问题可能来自于插入包含“0”增量的数据，而这将通过AUTO_INCREMENT转换为“1”，并导致此错误。
我的解决方案是在sql导入文件中使用。

SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';

赞(0）回复(0）举报 2022-12-10

42fyovps4#

如果您尝试从SQL转储填充表，请确保转储的“INSERT INTO”语句中列出的表与您尝试填充的表相同。如果转储尝试将条目放入“MyOtherTable”（可能已经有条目），则打开“MyTable”并使用SQL转储进行导入将引发此类错误。

赞(0）回复(0）举报 2022-12-10

5t7ly7z55#

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;

赞(0）回复(0）举报 2022-12-10

ymdaylpp6#

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!

赞(0）回复(0）举报 2022-12-10

6uxekuva7#

当我遇到这种错误时，我不得不将数据类型更新一个等级。例如，如果我将其作为“tiny int”，则将其更改为“small int”~ Nita

赞(0）回复(0）举报 2022-12-10

p5fdfcr18#

我刚刚遇到了同样的问题，但在这里，它似乎来自这样一个事实，即我声明的ID列是无符号的，并结合一个ID值'0'（零）导致导入失败...
因此，通过将我声明为“0”的每个ID（PK列）的值和每个对应的FK更改为新值，我的问题得到了解决。

赞(0）回复(0）举报 2022-12-10