Check for referential integrity break

In my process, I do something like:

SET FOREIGN_KEY_CHECKS = 0;
LOAD DATA INFILE '/path/to/mytable.txt' INTO TABLE mytable;
SET FOREIGN_KEY_CHECKS = 1;

Now, I need to check that the data after this import is not breaking the referential integrity. I would like to do something like

check d开发者_Go百科atabase all foreign_keys;

Is a similar command exists? If not, how to do this control?

Environment: MySQL v5.1.xx with InnoDB

Thanks

Answer

Here is some code which does what you need. It looks like there's no such command.

History

OK, I'm not a MySQL expert but referential integrity is managed constantly unless you disable it. You cannot insert a row into a table which violates a constraint unless you've dropped or disabled the constraint first. There's no need to "check" them.

If you did "disable" them, then enabling them will force a check.

This is in fact completely wrong and very scary indeed. at least in 5.1

I think if they had that function, they would just call it when you re-enabled the constraints, so I doubt you'll find it in the server.

The above link is dead, sadly.

The script mentioned in this blog post does a nice job of showing FKs which aren't referenced (though it will also show them when the FK is nullable, so may be legitimately null, so not always helpful!):

http://www.mysqlperformanceblog.com/2011/11/18/eventual-consistency-in-mysql/