Fixed: "ERROR 1062 (23000): Duplicate entry 'adapters' for key 'categories.IX_categories_Name'" in MySQL

ERROR 1062 (23000): Duplicate entry … for key … means a unique index refused a value that already exists. Usually that's the index doing its job. The surprise in my case was which value counted as a duplicate: MySQL refused adapters because Adapters was already there. This post explains why, how to choose the behaviour you want, and how to handle the error cleanly in MySQL and in a .NET C# API.

What I Was Doing

I had just added a unique index on the Name column of a categories table, through an EF Core migration (HasIndex(c => c.Name).IsUnique()), on MySQL 9.7. The table had one category, Adapters. From the mysql client, I inserted that name again, then the same word in lowercase:

mysql>

INSERT INTO categories (Name, CreatedAtUtc) VALUES ('Adapters', UTC_TIMESTAMP(6));
INSERT INTO categories (Name, CreatedAtUtc) VALUES ('adapters', UTC_TIMESTAMP(6));

What I Expected

The first insert to fail, since that exact name exists. The second to succeed: adapters and Adapters are different strings.

What I Got

Both failed:

Output

ERROR 1062 (23000): Duplicate entry 'Adapters' for key 'categories.IX_categories_Name'
ERROR 1062 (23000): Duplicate entry 'adapters' for key 'categories.IX_categories_Name'

A third name, Chargers, went in without a problem.

What I Checked

The message has three parts. Duplicate entry 'adapters' is the value I tried to insert. for key 'categories.IX_categories_Name' is the unique index that refused it, written as table.index. And 23000 is the SQLSTATE, the standard code for an integrity-constraint violation. The MySQL error reference lists it as "Error number: 1062; Symbol: ER_DUP_ENTRY; SQLSTATE: 23000".

So the index treated adapters as equal to Adapters. Unique indexes compare values the way the column compares text, so I looked at the column's collation:

mysql>

SHOW FULL COLUMNS FROM categories;

Output (the Name row)

| Name         | varchar(100) | utf8mb4_0900_ai_ci | NO   | UNI | NULL    |                | select,insert,update,references |         |

Why It Happens

utf8mb4_0900_ai_ci is MySQL's default collation, and its suffix says how it compares: ai, accent-insensitive, and ci, case-insensitive. To this column, Adapters, adapters and ADAPTERS are the same value, so the unique index allows only one of them.

For category names that's usually what you want: two categories that differ only in capital letters would confuse everyone. The error was right; my expectation was wrong.

The Fix

Pick the fix that matches what you meant.

If the names should be unique regardless of case (most cases)

Keep the index as it is, and treat the error as an answer: the category already exists. In a .NET C# API, EF Core throws a DbUpdateException whose inner exception is MySql.Data's MySqlException, with the MySQL error number in Number. Catch exactly 1062 and answer 409 Conflict:

Adding a category

try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateException ex) when (ex.InnerException is MySqlException { Number: 1062 })
{
    // 1062: a unique index refused a duplicate value.
    return Results.Conflict(new { error = $"A category named '{name}' already exists." });
}

MySqlException is in using MySql.Data.MySqlClient;. Without the catch, the API answered with an unhandled exception, whose first lines read:

Output (before the catch)

Microsoft.EntityFrameworkCore.DbUpdateException: Could not save changes. Please configure your entity type accordingly.
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry 'Chargers' for key 'categories.IX_categories_Name'

With it, the same kind of request gets a clean answer:

Terminal

curl -s -i -X POST "http://localhost:5190/try/categories?name=desks"

Output

HTTP/1.1 409 Conflict
Content-Type: application/json; charset=utf-8

{"error":"A category named 'desks' already exists."}

If an existing row should be updated instead (an upsert)

Sometimes a duplicate isn't an error but an instruction: "add 5 to this SKU's stock, and create it if it's new". MySQL's INSERT … ON DUPLICATE KEY UPDATE does that in one statement. The manual's page describes it: "If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs."

mysql>

INSERT INTO products (Sku, Name, CategoryId, UnitPrice, QuantityOnHand, ReorderLevel, IsArchived, CreatedAtUtc, UpdatedAtUtc)
VALUES ('DSK-STD-01', 'Standing desk', 1, 349.00, 5, 1, 0, UTC_TIMESTAMP(6), UTC_TIMESTAMP(6)) AS new
ON DUPLICATE KEY UPDATE QuantityOnHand = products.QuantityOnHand + new.QuantityOnHand;

Output

Query OK, 2 rows affected (0.004 sec)

The SKU DSK-STD-01 existed with 0 in stock; afterwards it had 5. AS new names the row you tried to insert, so new.QuantityOnHand is the 5 from VALUES. "2 rows affected" doesn't mean two rows: the manual explains that "the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values."

If different capitalisation should count as different names

Then the column needs a case-sensitive collation, such as utf8mb4_0900_as_cs (accent- and case-sensitive). That's a schema change, and with EF Core and Oracle's MySQL provider it needs one extra step. EF Core's UseCollation("utf8mb4_0900_as_cs") puts the collation into the migration's C# code, but the provider leaves it out of the SQL it runs: I applied such a migration, dotnet ef printed Done., and the column was still utf8mb4_0900_ai_ci. Declare the collation with the provider's own ForMySQLHasCollation("utf8mb4_0900_as_cs") instead, and write the statement in the migration yourself, because for an existing column the provider generates an empty migration. Fixed: EF Core UseCollation() Doesn't Change the MySQL Column Collation shows how I found that. The migration:

The migration's Up and Down methods

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(
        "ALTER TABLE `categories` MODIFY `Name` varchar(100) NOT NULL COLLATE utf8mb4_0900_as_cs;");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(
        "ALTER TABLE `categories` MODIFY `Name` varchar(100) NOT NULL COLLATE utf8mb4_0900_ai_ci;");
}

After that migration, SHOW FULL COLUMNS FROM categories LIKE 'Name'; reported utf8mb4_0900_as_cs, and desks was accepted next to Desks. Going back has a catch: the Down method failed with Duplicate entry 'desks' for key 'categories.IX_categories_Name', because the case-insensitive index can't hold both names. Remove or rename one of them first. Think twice before choosing case-sensitive names that people type: Desks and desks side by side in a list looks like a bug.

After the Fix

The unique index stays, so no two categories can share a name in any capitalisation. The API answers a duplicate with 409 Conflict and a readable message, and stock deliveries use an upsert instead of failing on existing SKUs.

Common Mistakes

Mistake	What happens	Do this instead
Checking "does it exist?" in code, then inserting	Two requests at the same moment both pass the check, and one still fails with 1062	Rely on the unique index and catch 1062
Catching every DbUpdateException as a duplicate	Other errors get a wrong 409	Filter on Number: 1062
Dropping the unique index to stop the error	Duplicates get in	Keep the index; handle the error
Expecting case-sensitive comparison	adapters is rejected	Know your collation; _ci means case-insensitive

Troubleshooting

Symptom	Likely cause	Fix
for key 'PRIMARY'	You inserted an Id that already exists	Let AUTO_INCREMENT choose the Id
The duplicate isn't visible in the table	The values differ only in case or accents	Compare with the collation in mind, for example WHERE Name = 'adapters'
1062 when updating, not inserting	The update changes a value into one that already exists	Check the new value, or catch 1062 there too

FAQ

Why does the key name include the table?

MySQL writes the index as table.index in this message, so you can tell which table's index refused the value when a statement touches several.

Does a failed insert waste an AUTO_INCREMENT number?

It did here: the two rejected inserts used up Ids 3 and 4, and the next category got 5. Gaps in AUTO_INCREMENT Ids are normal; never rely on them being continuous.

Conclusion

ERROR 1062 (23000): Duplicate entry 'x' for key 'table.index' means a unique index refused a value that already exists, compared the column's way. With the default utf8mb4_0900_ai_ci collation, that comparison ignores case and accents, which is why adapters clashed with Adapters. Keep the index, return 409 Conflict from your API by catching MySqlException number 1062, and use INSERT … ON DUPLICATE KEY UPDATE when a duplicate should update the row instead.

Additional Resources

• Server Error Message Reference — error 1062.
• INSERT … ON DUPLICATE KEY UPDATE Statement — upserts and the affected-rows count.
• Character Sets and Collations in MySQL — what a collation decides.

Related

• How to Create a Unique Index with EF Core and MySQL
• How to Check the MySQL Character Set and Collation
• How to List the Indexes of a MySQL Table