Identifying which value of a multi-row inserts fails foreign key constraint
Posted
by Jonathan
on Stack Overflow
See other posts from Stack Overflow
or by Jonathan
Published on 2010-04-20T02:20:59Z
Indexed on
2010/04/20
2:23 UTC
Read the original article
Hit count: 321
mysql
|mysql-connector
I have a multi-row insert that looks something like:
insert into table VALUES
(1, 2, 3),
(4, 5, 6),
(7, 8, 9);
Assume the first attribute (1, 4, 7) is a foreign key to another table and assume that this referenced table does not have the value '4'. A MySQLExeption is thrown with error code 1452.
EXCEPTION: Cannot add or update a child row: a foreign key constraint fails (dbName/tableName
, CONSTRAINT id
FOREIGN KEY (customer_id
) REFERENCES referencedTable
(customer_id
))
Is there a way to identify which value caused the error? I would love to give my user an error message that said something like:
Error: '4' does not exist in the referenced table.
I am using the .NET mysql connector to execute the insert.
Thanks-
Jonathan
© Stack Overflow or respective owner