Check for referential integrity break

Question

Hi All,

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 database all foreign_keys;

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

Environment: MySQL v5.1.xx with InnoDB

Thanks

Answer 1

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.