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

Question

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?

Answer 1

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:

1. Database name pattern (LIKE style)
2. Table name pattern (LIKE style)

3. 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).

Answer 2

Thanks for this great answer - this is a very handy tool. Here is a slightly modified version of the procedure that includes SQL in the output table to delete keys with invalid keys - handy for the cases where you have confirmed that these rows are simply orphans from missing/disabled delete cascade rules (and not orphans from primary key changes or other more complex cases).

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),                 `INVALID_KEY_DELETE_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),                 `INVALID_KEY_DELETE_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, ';'),                     `INVALID_KEY_DELETE_SQL` = CONCAT('DELETE ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '` ',                         'FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' ',                          'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' ',                          'ON (', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', '.`', COLUMN_NAME_VAR, '`', ' = ', '`', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',                          'WHERE ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',                         'AND ', '`', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL', ';');             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; 

Answer 3

I modified the script to handle multiple columns foreign keys.

DELIMITER $$

DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$

CREATE PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
    IN `checked_database_name` VARCHAR(64),
    IN `checked_table_name` VARCHAR(64),
    IN `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 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;

    SET @prev_constraint_name = '';
    SET @prev_table_schema = '';
    SET @prev_table_name = '';
    SET @prev_referenced_table_schema = '';
    SET @prev_referenced_table_name = '';

    SET @from_part = '';
    SET @where_part = '';
    SET @where_nullable = '';

    SET @all_columns = '';
    SET @all_referenced_columns = '';

    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;

        IF (@prev_constraint_name <> CONSTRAINT_NAME_VAR AND @from_part <> '' AND @where_part <> '') THEN

            SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ') INTO @invalid_key_count;');
            SET @invalid_query = CONCAT('SELECT * ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ')');
            PREPARE stmt FROM @full_query;

            EXECUTE stmt;
            IF @invalid_key_count > 0 THEN
                INSERT INTO 
                    INVALID_FOREIGN_KEYS 
                SET 
                    `TABLE_SCHEMA` = @prev_table_schema, 
                    `TABLE_NAME` = @prev_table_name, 
                    `COLUMN_NAME` = @all_columns, 
                    `CONSTRAINT_NAME` = @prev_constraint_name, 
                    `REFERENCED_TABLE_SCHEMA` = @prev_referenced_table_schema, 
                    `REFERENCED_TABLE_NAME` = @prev_table_name, 
                    `REFERENCED_COLUMN_NAME` = @all_referenced_columns, 
                    `INVALID_KEY_COUNT` = @invalid_key_count,
                    `INVALID_KEY_SQL` = @invalid_query;
            END IF;
            DEALLOCATE PREPARE stmt; 

            SET @where_part = '';
            SET @where_nullable = '';

            SET @all_columns = '';
            SET @all_referenced_columns = '';
        END IF;

        IF (LENGTH(@where_part) > 0) THEN
            SET @where_nullable = CONCAT(@where_nullable, ' OR ');
            SET @where_part = CONCAT(@where_part, ' AND ');

            SET @all_columns = CONCAT(@all_columns, ', ' COLUMN_NAME_VAR);
            SET @all_referenced_columns = CONCAT(@all_referenced_columns, ', ' REFERENCED_COLUMN_NAME_VAR);
        ELSE
            SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ');
            SET @from_where_part = CONCAT('NOT EXISTS (SELECT * FROM `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ');

            SET @all_columns = COLUMN_NAME_VAR;
            SET @all_referenced_columns = REFERENCED_COLUMN_NAME_VAR;
        END IF;

        SET @where_nullable = CONCAT(@where_nullable, 'REFERRING.', COLUMN_NAME_VAR, ' IS NOT NULL');
        SET @where_part = CONCAT(@where_part, 'REFERRING.', COLUMN_NAME_VAR, ' = ', 'REFERRED.', REFERENCED_COLUMN_NAME_VAR);

        SET @prev_constraint_name = CONSTRAINT_NAME_VAR;
        SET @prev_table_schema = TABLE_SCHEMA_VAR;
        SET @prev_table_name = TABLE_NAME_VAR;
        SET @prev_referenced_table_schema = REFERENCED_TABLE_SCHEMA_VAR;
        SET @prev_referenced_table_name = REFERENCED_TABLE_NAME_VAR;

    END LOOP foreign_key_cursor_loop;

    IF (@where_part <> '' AND @from_part <> '') THEN

        SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ') INTO @invalid_key_count;');
        SET @invalid_query = CONCAT('SELECT * ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ')');
        PREPARE stmt FROM @full_query;

        EXECUTE stmt;
        IF @invalid_key_count > 0 THEN
            INSERT INTO 
                INVALID_FOREIGN_KEYS 
            SET 
                `TABLE_SCHEMA` = @prev_table_schema, 
                `TABLE_NAME` = @prev_table_name, 
                `COLUMN_NAME` = @all_columns, 
                `CONSTRAINT_NAME` = @prev_constraint_name, 
                `REFERENCED_TABLE_SCHEMA` = @prev_referenced_table_schema, 
                `REFERENCED_TABLE_NAME` = @prev_table_name, 
                `REFERENCED_COLUMN_NAME` = @all_referenced_columns, 
                `INVALID_KEY_COUNT` = @invalid_key_count,
                `INVALID_KEY_SQL` = @invalid_query;
        END IF;
        DEALLOCATE PREPARE stmt; 
    END IF;
END$$

DELIMITER ;

CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;

SELECT * FROM INVALID_FOREIGN_KEYS;

Answer 4

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.