Fixed: "ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails" in MySQL

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails means you inserted or updated a row that points at a parent which doesn't exist. I got it from my own mistake: I guessed that a new category had Id 3, because it was the third one I'd created. It wasn't. This post shows why the guess was wrong, how to find the right Id, how to insert without guessing, and how to answer 400 Bad Request from a .NET C# API.

What I Was Doing

In an inventory database on MySQL 9.7, products belong to categories: each product's CategoryId must be the Id of a row in categories. I had just created a category called Chargers from the mysql client. Two categories had been created before it, so I assumed it was number 3, and added a product to it:

mysql>

INSERT INTO products (Sku, Name, CategoryId, UnitPrice, QuantityOnHand, ReorderLevel, IsArchived, CreatedAtUtc, UpdatedAtUtc)
VALUES ('CHG-USBC-65W', 'USB-C charger, 65 W', 3, 39.00, -3, 2, 0, UTC_TIMESTAMP(6), UTC_TIMESTAMP(6));

What I Expected

Query OK, 1 row affected.

What I Got

Output

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`inventory`.`products`, CONSTRAINT `FK_products_categories_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `categories` (`Id`) ON DELETE RESTRICT)

What I Checked

The bracketed part names the foreign key: products.CategoryId must match a categories.Id. So, which categories exist?

mysql>

SELECT Id, Name FROM categories;

Output

+----+----------+
| Id | Name     |
+----+----------+
|  2 | Adapters |
|  5 | Chargers |
+----+----------+
2 rows in set (0.000 sec)

Chargers is number 5, and there's no category 3. Asking MySQL for the table's next number confirms the counter had moved past 3 and 4:

mysql>

SELECT AUTO_INCREMENT FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'inventory' AND TABLE_NAME = 'categories';

Output

+----------------+
| AUTO_INCREMENT |
+----------------+
|              6 |
+----------------+
1 row in set (0.002 sec)

Where did 3 and 4 go? Just before creating Chargers, I had tested a unique index on the category name by inserting two duplicate names, and both inserts failed with error 1062. Each failed insert had still taken a number.

Why It Happens

MySQL's error reference lists the error as "Error number: 1452; Symbol: ER_NO_REFERENCED_ROW_2; SQLSTATE: 23000" and explains: "InnoDB reports this error when you try to add a row but there is no parent row, and a foreign key constraint fails. Add the parent row first."

The parent row here did exist; I had the wrong number for it. The manual's AUTO_INCREMENT page explains why Ids skip: "Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused." And so: "there may be gaps in the values stored in an AUTO_INCREMENT column of a table." My two failed duplicate inserts left exactly such a gap.

Interestingly, the product insert that failed with 1452 did not use up a product number: the next product I added got the next Id. That's new in MySQL 9.6 and later, where the server checks foreign keys itself. The same page: "an insert rejected by a foreign key constraint is validated before the InnoDB row operation and does not consume an AUTO_INCREMENT value."

The Fix

Never guess an AUTO_INCREMENT Id. Look the parent up by something stable, such as its unique name, in the same statement:

mysql>

INSERT INTO products (Sku, Name, CategoryId, UnitPrice, QuantityOnHand, ReorderLevel, IsArchived, CreatedAtUtc, UpdatedAtUtc)
VALUES ('CHG-USBC-65W', 'USB-C charger, 65 W', (SELECT Id FROM categories WHERE Name = 'Chargers'), 39.00, -3, 2, 0, UTC_TIMESTAMP(6), UTC_TIMESTAMP(6));

Output

Query OK, 1 row affected (0.002 sec)

mysql>

SELECT Id, Sku, CategoryId, QuantityOnHand FROM products;

Output

+----+--------------+------------+----------------+
| Id | Sku          | CategoryId | QuantityOnHand |
+----+--------------+------------+----------------+
|  3 | ADP-HDMI-01  |          2 |              8 |
|  4 | ADP-HDMI-02  |          2 |              4 |
|  5 | CHG-USBC-65W |          5 |             -3 |
+----+--------------+------------+----------------+
3 rows in set (0.000 sec)

In application code, the same rule applies: use the Id that the insert returned, or the one a user picked from a list the database produced. With EF Core, after SaveChangesAsync, the new entity's Id property holds the number MySQL assigned.

Handle it in a .NET C# API

When a client sends a categoryId that doesn't exist, the insert fails the same way, wrapped in EF Core's DbUpdateException with MySql.Data's MySqlException inside. Catch number 1452 and answer 400 Bad Request, since the request itself was wrong:

Saving a product

try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateException ex) when (ex.InnerException is MySqlException { Number: 1452 })
{
    // 1452: the foreign key found no category with this Id.
    return Results.BadRequest(new { error = $"There is no category with Id {categoryId}." });
}

Terminal

curl -s -i -X POST "http://localhost:5190/try/products?sku=DSK-STD-03&name=Desk%20lamp&categoryId=99&unitPrice=25"

Output

HTTP/1.1 400 Bad Request
Content-Type: application/json; charset=utf-8

{"error":"There is no category with Id 99."}

MySqlException is in using MySql.Data.MySqlClient;.

After the Fix

The product was added to Chargers, with the right CategoryId of 5, and the API turns an unknown category into a 400 with a message the caller can act on.

Common Mistakes

Mistake	What happens	Do this instead
Assuming Ids are 1, 2, 3 without gaps	The guess points at a missing or wrong parent	Look the Id up, or use the one the insert returned
SET foreign_key_checks = 0 to get the insert in	A product points at a category that doesn't exist	Fix the Id
Inserting children before their parent in a script	1452 on the first child	Insert parents first, as the error reference says
Catching every DbUpdateException as 400	Duplicates and other errors get the wrong answer	Filter on Number: 1452

Troubleshooting

Symptom	Likely cause	Fix
1452 on an UPDATE	You changed a foreign key column to a value with no parent	Use an existing parent's Id

FAQ

Can I reset AUTO_INCREMENT to close the gap?

You can, but don't: the manual says lost values "are not reused", and nothing should depend on Ids being continuous. An Id is a label, not a count.

What's the opposite error, when deleting?

ERROR 1451 (23000): Cannot delete or update a parent row: the parent you're deleting still has children. It has its own post.

Conclusion

ERROR 1452 (23000): Cannot add or update a child row means the row you're saving points at a parent Id that doesn't exist. Read the constraint in the message, list the parents, and don't guess AUTO_INCREMENT Ids: failed inserts leave gaps. Look the parent up by a unique value, or use the Id the database returned, and in a .NET C# API, catch MySqlException number 1452 and return 400 Bad Request.

Additional Resources

• Server Error Message Reference — error 1452.
• AUTO_INCREMENT Handling in InnoDB — why Ids have gaps.
• FOREIGN KEY Constraints — how MySQL checks them.

Related

• Fixed: "ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails" in MySQL
• Fixed: "ERROR 1062 (23000): Duplicate entry 'adapters' for key 'categories.IX_categories_Name'" in MySQL
• How to List the Foreign Keys of a MySQL Table