Force InnoDB to recheck foreign keys on a table/tables?

DELIMITER $$ DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$ CREATE PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`( checked_database_name VARCHAR(64), checked_table_name VARCHAR(64), temporary_result_table ENUM('Y', 'N')) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA BEGIN DECLARE TABLE_SCHEMA_VAR VARCHAR(64); DECLARE TABLE_NAME_VAR VARCHAR(64); DECLARE COLUMN_NAME_VAR VARCHAR(64); DECLARE CONSTRAINT_NAME_VAR VARCHAR(64); DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64); DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64); DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64); DECLARE KEYS_SQL_VAR VARCHAR(1024); DECLARE done INT DEFAULT 0; DECLARE foreign_key_cursor CURSOR FOR SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `COLUMN_NAME`, `CONSTRAINT_NAME`, `REFERENCED_TABLE_SCHEMA`, `REFERENCED_TABLE_NAME`, `REFERENCED_COLUMN_NAME` FROM information_schema. KEY_COLUMN_USAGE WHERE `CONSTRAINT_SCHEMA` LIKE checked_database_name AND `TABLE_NAME` LIKE checked_table_name AND `REFERENCED_TABLE_SCHEMA` IS NOT NULL; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; IF temporary_result_table = 'N' THEN DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS; DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS; CREATE TABLE INVALID_FOREIGN_KEYS( `TABLE_SCHEMA` VARCHAR(64), `TABLE_NAME` VARCHAR(64), `COLUMN_NAME` VARCHAR(64), `CONSTRAINT_NAME` VARCHAR(64), `REFERENCED_TABLE_SCHEMA` VARCHAR(64), `REFERENCED_TABLE_NAME` VARCHAR(64), `REFERENCED_COLUMN_NAME` VARCHAR(64), `INVALID_KEY_COUNT` INT, `INVALID_KEY_SQL` VARCHAR(1024) ); ELSEIF temporary_result_table = 'Y' THEN DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS; DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS; CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS( `TABLE_SCHEMA` VARCHAR(64), `TABLE_NAME` VARCHAR(64), `COLUMN_NAME` VARCHAR(64), `CONSTRAINT_NAME` VARCHAR(64), `REFERENCED_TABLE_SCHEMA` VARCHAR(64), `REFERENCED_TABLE_NAME` VARCHAR(64), `REFERENCED_COLUMN_NAME` VARCHAR(64), `INVALID_KEY_COUNT` INT, `INVALID_KEY_SQL` VARCHAR(1024) ); END IF; OPEN foreign_key_cursor; foreign_key_cursor_loop: LOOP FETCH foreign_key_cursor INTO TABLE_SCHEMA_VAR, TABLE_NAME_VAR, COLUMN_NAME_VAR, CONSTRAINT_NAME_VAR, REFERENCED_TABLE_SCHEMA_VAR, REFERENCED_TABLE_NAME_VAR, REFERENCED_COLUMN_NAME_VAR; IF done THEN LEAVE foreign_key_cursor_loop; END IF; SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`. `', TABLE_NAME_VAR, '`', ' AS REFERRING ', 'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.

`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ', 'ON (REFERRING', '. `', COLUMN_NAME_VAR, '`', ' = ', 'REFERRED', '. `', REFERENCED_COLUMN_NAME_VAR, '`', ') ', 'WHERE REFERRING', '.

`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ', 'AND REFERRED', '. `', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL'); SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;'); PREPARE stmt FROM @full_query; EXECUTE stmt; IF @invalid_key_count > 0 THEN INSERT INTO INVALID_FOREIGN_KEYS SET `TABLE_SCHEMA` = TABLE_SCHEMA_VAR, `TABLE_NAME` = TABLE_NAME_VAR, `COLUMN_NAME` = COLUMN_NAME_VAR, `CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR, `REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR, `REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR, `REFERENCED_COLUMN_NAME` = REFERENCED_COLUMN_NAME_VAR, `INVALID_KEY_COUNT` = @invalid_key_count, `INVALID_KEY_SQL` = CONCAT('SELECT ', 'REFERRING. ', '`', COLUMN_NAME_VAR, '` ', 'AS "Invalid: ', COLUMN_NAME_VAR, '", ', 'REFERRING.

* ', @from_part, ';'); END IF; DEALLOCATE PREPARE stmt; END LOOP foreign_key_cursor_loop; END$$ DELIMITER ; CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y'); DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS; SELECT * FROM INVALID_FOREIGN_KEYS 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 of ANALYZE_INVALID_FOREIGN_KEYS : Database name pattern (LIKE style) Table name pattern (LIKE style) Whether the result will be temporary.It can be: Y N NULL In case of Y the ANALYZE_INVALID_FOREIGN_KEYS result table will be temporary table.

The temporary table won't be visible for other sessions. You can execute multiple ANALYZE_INVALID_FOREIGN_KEYS(...) stored procedure parallelly with temporary result table But if you are interested in the partial result from an other session, then you must use N then execute SELECT * 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 for CREATE TABLE and DROP TABLE so the creation of result table would cause problem in transaction.In this case you must create the result table yourself out of BEGIN; COMMIT/ROLLBACK block: CREATE TABLE INVALID_FOREIGN_KEYS( `TABLE_SCHEMA` VARCHAR(64), `TABLE_NAME` VARCHAR(64), `COLUMN_NAME` VARCHAR(64), `CONSTRAINT_NAME` VARCHAR(64), `REFERENCED_TABLE_SCHEMA` VARCHAR(64), `REFERENCED_TABLE_NAME` VARCHAR(64), `REFERENCED_COLUMN_NAME` VARCHAR(64), `INVALID_KEY_COUNT` INT, `INVALID_KEY_SQL` VARCHAR(1024) ) 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 of INVALID_KEY_SQL column of INVALID_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).

DELIMITER $$ DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$ CREATE PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`( checked_database_name VARCHAR(64), checked_table_name VARCHAR(64), temporary_result_table ENUM('Y', 'N')) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA BEGIN DECLARE TABLE_SCHEMA_VAR VARCHAR(64); DECLARE TABLE_NAME_VAR VARCHAR(64); DECLARE COLUMN_NAME_VAR VARCHAR(64); DECLARE CONSTRAINT_NAME_VAR VARCHAR(64); DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64); DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64); DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64); DECLARE KEYS_SQL_VAR VARCHAR(1024); DECLARE done INT DEFAULT 0; DECLARE foreign_key_cursor CURSOR FOR SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `COLUMN_NAME`, `CONSTRAINT_NAME`, `REFERENCED_TABLE_SCHEMA`, `REFERENCED_TABLE_NAME`, `REFERENCED_COLUMN_NAME` FROM information_schema. KEY_COLUMN_USAGE WHERE `CONSTRAINT_SCHEMA` LIKE checked_database_name AND `TABLE_NAME` LIKE checked_table_name AND `REFERENCED_TABLE_SCHEMA` IS NOT NULL; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; IF temporary_result_table = 'N' THEN DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS; DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS; CREATE TABLE INVALID_FOREIGN_KEYS( `TABLE_SCHEMA` VARCHAR(64), `TABLE_NAME` VARCHAR(64), `COLUMN_NAME` VARCHAR(64), `CONSTRAINT_NAME` VARCHAR(64), `REFERENCED_TABLE_SCHEMA` VARCHAR(64), `REFERENCED_TABLE_NAME` VARCHAR(64), `REFERENCED_COLUMN_NAME` VARCHAR(64), `INVALID_KEY_COUNT` INT, `INVALID_KEY_SQL` VARCHAR(1024) ); ELSEIF temporary_result_table = 'Y' THEN DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS; DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS; CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS( `TABLE_SCHEMA` VARCHAR(64), `TABLE_NAME` VARCHAR(64), `COLUMN_NAME` VARCHAR(64), `CONSTRAINT_NAME` VARCHAR(64), `REFERENCED_TABLE_SCHEMA` VARCHAR(64), `REFERENCED_TABLE_NAME` VARCHAR(64), `REFERENCED_COLUMN_NAME` VARCHAR(64), `INVALID_KEY_COUNT` INT, `INVALID_KEY_SQL` VARCHAR(1024) ); END IF; OPEN foreign_key_cursor; foreign_key_cursor_loop: LOOP FETCH foreign_key_cursor INTO TABLE_SCHEMA_VAR, TABLE_NAME_VAR, COLUMN_NAME_VAR, CONSTRAINT_NAME_VAR, REFERENCED_TABLE_SCHEMA_VAR, REFERENCED_TABLE_NAME_VAR, REFERENCED_COLUMN_NAME_VAR; IF done THEN LEAVE foreign_key_cursor_loop; END IF; SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`. `', TABLE_NAME_VAR, '`', ' AS REFERRING ', 'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.

`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ', 'ON (REFERRING', '. `', COLUMN_NAME_VAR, '`', ' = ', 'REFERRED', '. `', REFERENCED_COLUMN_NAME_VAR, '`', ') ', 'WHERE REFERRING', '.

`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ', 'AND REFERRED', '. `', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL'); SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;'); PREPARE stmt FROM @full_query; EXECUTE stmt; IF @invalid_key_count > 0 THEN INSERT INTO INVALID_FOREIGN_KEYS SET `TABLE_SCHEMA` = TABLE_SCHEMA_VAR, `TABLE_NAME` = TABLE_NAME_VAR, `COLUMN_NAME` = COLUMN_NAME_VAR, `CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR, `REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR, `REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR, `REFERENCED_COLUMN_NAME` = REFERENCED_COLUMN_NAME_VAR, `INVALID_KEY_COUNT` = @invalid_key_count, `INVALID_KEY_SQL` = CONCAT('SELECT ', 'REFERRING. ', '`', COLUMN_NAME_VAR, '` ', 'AS "Invalid: ', COLUMN_NAME_VAR, '", ', 'REFERRING.

* ', @from_part, ';'); END IF; DEALLOCATE PREPARE stmt; END LOOP foreign_key_cursor_loop; END$$ DELIMITER ; CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y'); DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS; SELECT * FROM INVALID_FOREIGN_KEYS; 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 of ANALYZE_INVALID_FOREIGN_KEYS: Database name pattern (LIKE style) Table name pattern (LIKE style) Whether the result will be temporary.It can be: 'Y', 'N', NULL.

In case of 'Y' the ANALYZE_INVALID_FOREIGN_KEYS result table will be temporary table. The temporary table won't be visible for other sessions. You can execute multiple ANALYZE_INVALID_FOREIGN_KEYS(...) stored procedure parallelly with temporary result table.

But if you are interested in the partial result from an other session, then you must use 'N', then execute SELECT * 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 for CREATE TABLE ... and DROP TABLE ..., so the creation of result table would cause problem in transaction.In this case you must create the result table yourself out of BEGIN; COMMIT/ROLLBACK; block: CREATE TABLE INVALID_FOREIGN_KEYS( `TABLE_SCHEMA` VARCHAR(64), `TABLE_NAME` VARCHAR(64), `COLUMN_NAME` VARCHAR(64), `CONSTRAINT_NAME` VARCHAR(64), `REFERENCED_TABLE_SCHEMA` VARCHAR(64), `REFERENCED_TABLE_NAME` VARCHAR(64), `REFERENCED_COLUMN_NAME` VARCHAR(64), `INVALID_KEY_COUNT` INT, `INVALID_KEY_SQL` VARCHAR(1024) ); 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 of INVALID_KEY_SQL column of INVALID_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).

Great answer. Welcome to Stack Overflow! – Jim Blackler May 12 at 12:45 Very nice grow-your-own solution!

The only thing I could improve on is that "analyze" is spelled with a "y". – pcronin May 13 at 6:50.

There is no tool, that can do that. But you can write a script, that will walk through all your tables, drop and recreate foreign key constrains. On recreation, there will be an error if something is wrong.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.