Why Am I Getting a Duplicate Key Error When Creating a MySQL Table?

Duplicate Key Error in CREATE TABLE Command

Q1. Error 1022: Can't write; duplicate key in table

I'm experiencing an error 1022 when executing a CREATE TABLE command due to duplicate keys. I've reviewed the query, but I can't identify the cause.

Query:

CREATE TABLE IF NOT EXISTS  `apptwo`.`usercircle` (
 `idUserCircle` MEDIUMINT NOT NULL ,
 `userId` MEDIUMINT NULL ,
 `circleId` MEDIUMINT NULL ,
 `authUser` BINARY NULL ,
 `authOwner` BINARY NULL ,
 `startDate` DATETIME NULL ,
 `endDate` DATETIME NULL ,
 PRIMARY KEY (  `idUserCircle` ) ,
 INDEX  `iduser_idx` (  `userId` ASC ) ,
 INDEX  `idcategory_idx` (  `circleId` ASC ) ,
 CONSTRAINT  `iduser` FOREIGN KEY (  `userId` ) REFERENCES  `apptwo`.`user` (
`idUser`
) ON DELETE NO ACTION ON UPDATE NO ACTION ,
 CONSTRAINT  `idcategory` FOREIGN KEY (  `circleId` ) REFERENCES  `apptwo`.`circle` (
`idCircle`
) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = INNODB;

Q2. Where is the duplication occurring?

A1. Constraint Name Conflict

The error likely stems from duplicate constraint names. Constraints must be unique across the entire database, not just within a specific table.

In this case, it appears that there are already constraints named iduser and/or idcategory in the database. Renaming these constraints will resolve the issue.

Q3. How can I identify existing constraints?

A2. Query to Locate Constraints

To identify where the constraints are currently used, execute the following query:

SELECT `TABLE_SCHEMA`, `TABLE_NAME`
FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE `CONSTRAINT_NAME` IN ('iduser', 'idcategory');

This query will return the schema and table names where the constraints are applied.

The above is the detailed content of Why Am I Getting a Duplicate Key Error When Creating a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!