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?

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

Solution 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;

Solution 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;

Solution 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.

Solution 5

The same check but for invalid UNIQUE keys analysis:

--> Small bug/feature: It will report duplicate nulls also. (While mysql allows duplicate 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;

View more solutions

Comments

[Jim Blackler]

Great answer. Welcome to Stack Overflow!

[ClearCrescendo]

I found that I had to add 'collate utf8_general_ci' after the checked_* parameters in the cursors where clause because my information schema uses utf8_general_ci where as the login account uses the more correct utf8_unicode_ci. Without this I received a collation mismatch error on the like clause. "WHERE CONSTRAINT_SCHEMA LIKE checked_database_name collate utf8_general_ci AND TABLE_NAME LIKE checked_table_name collate utf8_general_ci AND REFERENCED_TABLE_SCHEMA IS NOT NULL;

[Likeyn]

I know it's old now but came across this and I'm wondering: any reason you're not using CONCAT('DELETE REFERRING ', @from_part, ';') for the DELETE statement?

[Nae]

@ClearCrescendo One can rid of that the LIKE collating error by enforcing using the same collations on both sides for example like: (CONSTRAINT_SCHEMA COLLATE utf8_unicode_ci) LIKE (checked_database_name COLLATE utf8_unicode_ci).

[Nae]

Note that one can add AND REFERENCED_TABLE_NAME = 'name_of_the_table_that_one_wants_to_check_children_for' to foreign_key_cursor 's WHERE for checking only a single table's children FKs.

[izogfif]

@Rayman @nailujed I'm getting Incorrect parameters in the call to native function 'CONCAT' error. Probably lines SET @all_referenced_columns = CONCAT(@all_referenced_columns, ', ' REFERENCED_COLUMN_NAME_VAR); and CONCAT(@all_columns, ', ' COLUMN_NAME_VAR); are incorrect. Did you forget to add a comma to separate arguments?