[ACCEPTED]-Force InnoDB to recheck foreign keys on a table/tables?-innodb

Accepted answer

Score: 112

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 19 the all database for invalid foreign keys. The 18 result will be loaded into INVALID_FOREIGN_KEYS table. Parameters 17 of ANALYZE_INVALID_FOREIGN_KEYS:

Database name pattern (LIKE style)
Table name pattern (LIKE style)
Whether the result will be temporary. It 16 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 15 table creation in transaction, because MySQL 14 executes implicit commit in transaction 13 for CREATE TABLE ... and DROP TABLE ..., so the creation of result table 12 would cause problem in transaction. In this 11 case you must create the result table yourself 10 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 9 commit: http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

The INVALID_FOREIGN_KEYS rows will contain only the 8 name of invalid database, table, column. But 7 you can see the invalid referring rows with 6 the execution of value of INVALID_KEY_SQL column of INVALID_FOREIGN_KEYS if 5 there is any.

This stored procedure will 4 be very fast if there are indexes on the 3 referring columns (aka. foreign index) and 2 on the referred columns (usually primary 1 key).

Score: 18

Thanks for this great answer - this is a 8 very handy tool. Here is a slightly modified 7 version of the procedure that includes SQL 6 in the output table to delete keys with 5 invalid keys - handy for the cases where 4 you have confirmed that these rows are simply 3 orphans from missing/disabled delete cascade 2 rules (and not orphans from primary key 1 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;

Score: 5

I modified the script to handle multiple 1 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;

Score: 2

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

Score: 1

The same check but for invalid UNIQUE keys 3 analysis:

--> Small bug/feature: It will report duplicate 2 nulls also. (While mysql allows duplicate 1 nulls).

DELIMITER $$

DROP PROCEDURE IF EXISTS ANALYZE_INVALID_UNIQUE_KEYS$$

CREATE
    PROCEDURE `ANALYZE_INVALID_UNIQUE_KEYS`(
        checked_database_name VARCHAR(64), 
        checked_table_name VARCHAR(64))

    LANGUAGE SQL
    NOT DETERMINISTIC
    READS SQL DATA

    BEGIN
        DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
        DECLARE TABLE_NAME_VAR VARCHAR(64);
        DECLARE COLUMN_NAMES_VAR VARCHAR(1000); 
        DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);

        DECLARE done INT DEFAULT 0;

        DECLARE unique_key_cursor CURSOR FOR
            select kcu.table_schema sch,
                   kcu.table_name tbl, 
                   group_concat(kcu.column_name) colName, 
                   kcu.constraint_name constName
            from 
                information_schema.table_constraints tc
            join 
                information_schema.key_column_usage kcu 
            on 
                kcu.constraint_name=tc.constraint_name 
                and kcu.constraint_schema=tc.constraint_schema
                and kcu.table_name=tc.table_name  
            where 
                kcu.table_schema like checked_database_name 
                and kcu.table_name like checked_table_name 
                and tc.constraint_type="UNIQUE" group by sch, tbl, constName;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        DROP TEMPORARY TABLE IF EXISTS INVALID_UNIQUE_KEYS;
        CREATE TEMPORARY TABLE INVALID_UNIQUE_KEYS(
            `TABLE_SCHEMA` VARCHAR(64), 
            `TABLE_NAME` VARCHAR(64), 
            `COLUMN_NAMES` VARCHAR(1000), 
            `CONSTRAINT_NAME` VARCHAR(64),
            `INVALID_KEY_COUNT` INT
        );



        OPEN unique_key_cursor;
        unique_key_cursor_loop: LOOP
            FETCH unique_key_cursor INTO 
            TABLE_SCHEMA_VAR, 
            TABLE_NAME_VAR, 
            COLUMN_NAMES_VAR, 
            CONSTRAINT_NAME_VAR;
            IF done THEN
                LEAVE unique_key_cursor_loop;
            END IF;

            SET @from_part = CONCAT('FROM (SELECT COUNT(*) counter FROM', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', 
                    ' GROUP BY ', COLUMN_NAMES_VAR , ') as s where s.counter > 1');
            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_UNIQUE_KEYS 
                SET 
                    `TABLE_SCHEMA` = TABLE_SCHEMA_VAR, 
                    `TABLE_NAME` = TABLE_NAME_VAR, 
                    `COLUMN_NAMES` = COLUMN_NAMES_VAR, 
                    `CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR, 
                    `INVALID_KEY_COUNT` = @invalid_key_count;
            END IF;
            DEALLOCATE PREPARE stmt; 

        END LOOP unique_key_cursor_loop;
    END$$

DELIMITER ;

CALL ANALYZE_INVALID_UNIQUE_KEYS('%', '%');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_UNIQUE_KEYS;

SELECT * FROM INVALID_UNIQUE_KEYS;

Source: stackoverflow.com

[ACCEPTED]-Force InnoDB to recheck foreign keys on a table/tables?-innodb

How to change MySQL Primary Key from signed to unsigned?

Unknown table engine 'InnoDB'

What is innodb_buffer_pool_size & innodb_log_file_size?

#1025 - Error on rename of './database/#sql-2e0f_1254ba7' to './database/table' (errno: 150)

MySQL InnoDB locks on joined rows

MySQL REPLACE in an auto incremented row

Prevent auto increment on MySQL duplicate insert

What are MySQL database engines?

What is the InnoDB equivalent of MyISAM's key_buffer_size?

MySQL Integer vs DateTime index