3

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

Alban Soupper
  • 671
  • 1
  • 5
  • 20

2 Answers2

3

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.

Stephanie Page
  • 3,875
  • 1
  • 18
  • 22
  • 1
    The command: SET FOREIGN_KEY_CHECKS = 0; disable globally all referential integrity checks. This allows to import a bunch of data without taking care of the order of table imports. The command: SET FOREIGN_KEY_CHECKS = 1; enable globally all referential integrity checks. But as specified in the documentation, (can't find the page now) this will not force a check on existing data... – Alban Soupper Sep 17 '10 at 07:55
  • Wow. that's some scary sh!t then. You have a constraint that looks declarative but cannot be relied upon. I'd really doubt that until proven otherwise. – Stephanie Page Sep 17 '10 at 15:24
  • Thanks, the script is exactly what I needed :) – Alban Soupper Sep 21 '10 at 07:38
1

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/

RedYeti
  • 1,024
  • 14
  • 28