I have a set of InnoDB
tables that I periodically need to maintain by removing some rows and inserting others. Several of the tables have foreign key constraints referencing other tables, so this means that the table loading order is important. To insert the new rows without worrying about the order of the tables, I use:
SET FOREIGN_KEY_CHECKS=0;
before, and then:
SET FOREIGN_KEY_CHECKS=1;
after.
When the loading is complete, I'd like to check that the data in the updated tables still hold referential integrity–that the new rows don't break foreign key constraints–but it seems that there's no way to do this.
As a test, I entered data that I was sure violated foreign key constraints, and upon re-enabling the foreign key checks, mysql produced no warnings or errors.
If I tried to find a way to specify the table loading order, and left the foreign key checks on during the loading process, this would not allow me to load data in a table that has a self-referencing foreign key constraint, so this would not be an acceptable solution.
Is there any way to force InnoDB to verify a table's or a database's foreign key constraints?
Best Answer
You can use this stored procedure to check the all database for invalid foreign keys. The result will be loaded into
INVALID_FOREIGN_KEYS
table. Parameters ofANALYZE_INVALID_FOREIGN_KEYS
:Whether the result will be temporary. It can be:
'Y'
,'N'
,NULL
.'Y'
theANALYZE_INVALID_FOREIGN_KEYS
result table will be temporary table. The temporary table won't be visible for other sessions. You can execute multipleANALYZE_INVALID_FOREIGN_KEYS(...)
stored procedure parallelly with temporary result table.'N'
, then executeSELECT * FROM INVALID_FOREIGN_KEYS;
from an other session.You must use
NULL
to skip result table creation in transaction, because MySQL executes implicit commit in transaction forCREATE TABLE ...
andDROP TABLE ...
, so the creation of result table would cause problem in transaction. In this case you must create the result table yourself out ofBEGIN; COMMIT/ROLLBACK;
block:Visit MySQL site about implicit commit: http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html
The
INVALID_FOREIGN_KEYS
rows will contain only the name of invalid database, table, column. But you can see the invalid referring rows with the execution of value ofINVALID_KEY_SQL
column ofINVALID_FOREIGN_KEYS
if there is any.This stored procedure will be very fast if there are indexes on the referring columns (aka. foreign index) and on the referred columns (usually primary key).