How to Force InnoDB to Recheck Foreign Keys After Loading Data?

How to Force InnoDB to Recheck Foreign Keys on a Table/Tables?

When loading data into tables with foreign key constraints, ensuring referential integrity is crucial. However, setting FOREIGN_KEY_CHECKS to 0 during the load can result in data inconsistencies after reenabling them.

To address this, consider using the provided stored procedure: ANALYZE_INVALID_FOREIGN_KEYS.

DELIMITER $$

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

        OPEN foreign_key_cursor;
        foreign_key_cursor_loop: LOOP
            FETCH foreign_key_cursor INTO 
            TABLE_SCHEMA_VAR, 
            ...

            SET @from_part = CONCAT(...);
            SET @full_query = CONCAT(...);
            PREPARE stmt FROM @full_query;

            EXECUTE stmt;
            IF @invalid_key_count > 0 THEN
                INSERT INTO 
                    INVALID_FOREIGN_KEYS 
                SET 
                    `TABLE_SCHEMA` = TABLE_SCHEMA_VAR, 
                    ...

            END IF;
            DEALLOCATE PREPARE stmt; 

        END LOOP foreign_key_cursor_loop;
    END$$

DELIMITER ;

This procedure analyzes all foreign keys and identifies any rows that violate them. The results are stored in the INVALID_FOREIGN_KEYS table.

To use this procedure:

• Execute CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y'); to analyze the entire database.
• Replace '%' with specific database and table patterns to narrow down the scope.
• Specify 'N' for temporary_result_table to store the results in a permanent table.

Note that this procedure requires indexes on both referring and referred columns for optimal performance.

The above is the detailed content of How to Force InnoDB to Recheck Foreign Keys After Loading Data?. For more information, please follow other related articles on the PHP Chinese website!