Identifying which value of a multi-row inserts fails foreign key constraint
- by Jonathan
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
