Bug #3443 Better foreign key constraint error messages
Submitted: 12 Apr 2004 2:19 Modified: 23 Sep 2005 17:53
Reporter: Matthias Urlichs Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:All OS:
Assigned to: Osku Salerma CPU Architecture:Any
Triage: D5 (Feature request)

[12 Apr 2004 2:19] Matthias Urlichs
Description:
The error

ERROR 1216: Cannot add or update a child row: a foreign key constraint fails

doesn't say which row contains the bad reference. That makes debugging the application needlessly difficult.

How to repeat:
obvious ...

Suggested fix:
Add the column and database.table names. For example:

ERROR 1216: Cannot add or update child row(BAR,BAZ) in table TEST.FOO: the foreign key constraint fails
[11 Nov 2004 16:08] Jörn Reder
I like to extend the "suggested fix":

Suggested fix:
Add the column, database.table, and _constraint_ names.

For example:
ERROR 1216: Cannot add or update child row(BAR,BAZ) in table TEST.FOO: the
foreign key constraint BARBAZCONSTR fails

Currently it's very cumbersome to debug those messages. I'd vote for increasing the priority of this feature request.
[24 Nov 2004 6:51] B Jones
I vote for this too. When you're loading many rows of data and get this message, not knowing which constraint, let alone which row!, makes this very difficult to track down!
[10 Dec 2004 0:09] B Jones
Having just done a lot of work with referential integrity in MySQL once again I vote to increase the priority of this request. Everytime we get this error we have to waste a lot of time trying to work out what caused the error. Put it this way: Everytime we hit this bug it costs us money!  We need something like Oracle where it actually tells you what constraint is violated.
[25 Feb 2005 15:22] Kristoffer Berggren
Im voting on this one also, I got this error message an hour ago and haven't yet been able to find the constraint. This is probely due too that Im working on a database where I do not have the initial SQL create statements available. It would be very nice if the error message could give a hint on what constraint that causes my inserts to fail.
[25 Feb 2005 15:25] Heikki Tuuri
Kristoffer,

please note that

SHOW INNODB STATUS

prints a detailed description of the latest FOREIGN KEY error.

Regards,

Heikki
[25 Feb 2005 15:46] Matthias Urlichs
Umm, if you already have the error textually, then passing it along to the connection's error handler shouldn't be too difficult ...

Anyway, MySQL is a multi-user database. "The last foreign key error" may not always be particularly helpful, especially when the error message is caused by a legacy application and/or logged somewhere, and can only be analyzed hours later. Been there, done that. :-/
[7 Sep 2005 12:24] Osku Salerma
Taking this.
[14 Sep 2005 7:42] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29797
[23 Sep 2005 8:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30251
[23 Sep 2005 13:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30267
[23 Sep 2005 13:48] Osku Salerma
Pushed to 5.0.14.
[23 Sep 2005 17:53] Paul Dubois
Noted in 5.0.14 changelog.
[26 Mar 2009 12:41] Thomas Bensler
My web hosting provider runs a 5.0.67 MySQL database and this bug seems to be present in that version. May somebody check whether the current production release has this bug or not?

Thx!