Here’s a MySQL scriptlet you won’t want to forget:

“select child.id from child left join parent on (child.parent_id=parent.id) where child.id is not null and parent.id is null;”

According to Dennis Haney, the original poster of this scriptlet (as found in his comment on this MySQL dev page here) you will get the error “ERROR 1216: Cannot add or update a child row: a foreign key constraint fails”, if a foreign key you are attempting to add is referencing a parent id that is NULL.  So, this scriptlet helps you find the problematic NULL identities.  Once the NULL is replaced with a valid value you can proceed to finish putting your foreign keys in place.

Leave a Reply