Duplicate key error in MySQL （Duplicate key name ''）

以你之姓@ 2022-08-26 01:19 6阅读 0赞

The below query is resulting in an error. I created this query in MySQL Workbench

Error
    
    SQL query:
    
    -- -----------------------------------------------------
    -- Table `smsdb`.`IntSupervisor`
    -- -----------------------------------------------------
      CREATE TABLE IF NOT EXISTS `smsdb`.`IntSupervisor` (
       `int_supr_id` VARCHAR( 32 ) NOT NULL ,
       `cent_id` INT NOT NULL ,
       INDEX `fk_IntSupervisor_Person1_idx` ( `int_supr_id` ASC ) ,
       INDEX `fk_IntSupervisor_Center1_idx` ( `cent_id` ASC ) ,
       PRIMARY KEY ( `int_supr_id` ) ,
       CONSTRAINT `fk_parent_id` FOREIGN KEY ( `int_supr_id` ) 
       REFERENCES `smsdb`.`Staff` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ,
       CONSTRAINT `fk_center_id` FOREIGN KEY ( `cent_id` )
       REFERENCES  `smsdb`.`Center` (`cent_id`
     ) ON DELETE CASCADE ON UPDATE RESTRICT
     ) ENGINE = InnoDB;

I got an error message on execution:

MySQL said: Documentation  
        #1022 - Can't write; duplicate key in table 'intsupervisor'

If anyone has any ideas on how I can resolve this issue, please guide me in the right direction. Thanks!

## Welcome to relational databases in MySQL! ;) ##

--------------------

You can't have two foreign keys named the same thing across the whole query.

PRIMARY KEY ( `int_supr_id` ) ,

CONSTRAINT `fk_parent_id` FOREIGN KEY ( `int_supr_id` )

In the above statement, you can't redefine the index type on a single column.

## So, how do I fix this annoying error? ##

--------------------

Based on the structure of the database, you need to remove one of the two lines above. I'm guessing your linking to another table from the one you are creating, so I recommend replacing ...

PRIMARY KEY ( `int_supr_id` ) ,
           CONSTRAINT `fk_parent_id` FOREIGN KEY ( `int_supr_id` )

With the following:

CONSTRAINT `fk_parent_id` FOREIGN KEY ( `int_supr_id` )

(if the above doesn't work, you likely need to specify a table name for the foreign key)

2.--  
\-- Table structure for table \`payment\`  
\--  
  
  
DROP TABLE IF EXISTS \`payment\`;  
SET @saved\_cs\_client = @@character\_set\_client;  
SET character\_set\_client = utf8;  
CREATE TABLE \`payment\` (  
\`ID\` bigint(20) NOT NULL AUTO\_INCREMENT,  
\`entry\_ID\` int(11) NOT NULL,  
\`account\_ID\` int(11) NOT NULL,  
\`amount\` double NOT NULL,  
\`pmt\_form\` varchar(20) NOT NULL,  
\`reference\` varchar(120) DEFAULT NULL,  
\`COID\` int(11) NOT NULL,  
PRIMARY KEY (\`ID\`),  
UNIQUE KEY \`ID\` (\`ID\`),  
KEY \`FK\_PAYMENT\_ACCOUNT\` (\`account\_ID\`),  
KEY \`FK\_PAYMENT\_ENTRY\` (\`entry\_ID\`),  
CONSTRAINT \`FK\_PAYMENT\_ACCOUNT\` FOREIGN KEY (\`account\_ID\`) REFERENCES \`account\` (\`ID\`),  
CONSTRAINT \`FK\_PAYMENT\_ENTRY\` FOREIGN KEY (\`entry\_ID\`)REFERENCES \`register\_entry\` (\`ID\`)  
) ENGINE=InnoDB AUTO\_INCREMENT=1 DEFAULT CHARSET=utf8;  
SET character\_set\_client = @saved\_cs\_client;

一开始一个是entry\_ID 一个是 entry\_id 导致报错。后来改为一样 导入数据通过