mysql - tipos - ¿Forzar a InnoDB a volver a verificar las claves externas en una tabla/tablas?

no se puede agregar o actualizar una fila secundaria falla una restricción de clave externa

Question

Tengo un conjunto de tablas InnoDB que necesito mantener periódicamente eliminando algunas filas e insertando otras. Algunas de las tablas tienen restricciones de clave externa que hacen referencia a otras tablas, por lo que esto significa que el orden de carga de la tabla es importante. Para insertar las nuevas filas sin preocuparse por el orden de las tablas, uso:

SET FOREIGN_KEY_CHECKS=0;

antes, y luego:

SET FOREIGN_KEY_CHECKS=1;

después.

Cuando finalice la carga, me gustaría comprobar que los datos de las tablas actualizadas aún conservan la integridad referencial, que las nuevas filas no rompen las restricciones de las claves externas, pero parece que no hay forma de hacerlo.

Como prueba, ingresé datos en los que estaba seguro de que violaba las restricciones de clave externa, y al volver a habilitar las comprobaciones de clave externa, mysql no produjo advertencias ni errores.

Si intentara encontrar una manera de especificar el orden de carga de la tabla, y dejé las comprobaciones de clave externa durante el proceso de carga, esto no me permitiría cargar datos en una tabla que tiene una restricción de clave externa de auto-referencia, por lo que No sea una solución aceptable.

¿Hay alguna forma de obligar a InnoDB a verificar las restricciones de clave externa de una tabla o de una base de datos?

Answer 1

Gracias por esta gran respuesta - esta es una herramienta muy útil. Aquí hay una versión ligeramente modificada del procedimiento que incluye SQL en la tabla de salida para eliminar claves con claves no válidas; útil para los casos en los que ha confirmado que estas filas son simplemente huérfanas de las reglas de cascada de eliminación desaparecidas / deshabilitadas (y no huérfanos de la primaria). Cambios clave u otros casos más complejos).

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 2

La misma comprobación pero para el análisis de claves ÚNICAS inválidas:

-> Pequeño error / característica : Informará nulos duplicados también. (Mientras mysql permite duplicar nulos).

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;

Answer 3

Modifiqué el script para manejar múltiples columnas de claves externas.

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
    SQL SECURITY DEFINER
    COMMENT ''''
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;

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

    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_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` = TABLE_SCHEMA_VAR, 
                    `TABLE_NAME` = TABLE_NAME_VAR, 
                    `COLUMN_NAME` = NULL, 
                    `CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR, 
                    `REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR, 
                    `REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR, 
                    `REFERENCED_COLUMN_NAME` = NULL, 
                    `INVALID_KEY_COUNT` = @invalid_key_count,
                    `INVALID_KEY_SQL` = @invalid_query;
            END IF;
            DEALLOCATE PREPARE stmt; 

            SET @where_part = '''';
            SET @where_nullable = '''';
        END IF;

        IF (LENGTH(@where_part) > 0) THEN
            SET @where_nullable = CONCAT(@where_nullable, '' OR '');
            SET @where_part = CONCAT(@where_part, '' AND '');
        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 '');
        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_name = CONSTRAINT_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` = TABLE_SCHEMA_VAR, 
                `TABLE_NAME` = TABLE_NAME_VAR, 
                `COLUMN_NAME` = NULL, 
                `CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR, 
                `REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR, 
                `REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR, 
                `REFERENCED_COLUMN_NAME` = NULL, 
                `INVALID_KEY_COUNT` = @invalid_key_count,
                `INVALID_KEY_SQL` = @invalid_query;
        END IF;
        DEALLOCATE PREPARE stmt; 
    END IF;
END

Answer 4

No hay ninguna herramienta que pueda hacer eso. Pero puede escribir un script, que recorrerá todas las tablas, eliminará y volverá a crear restricciones de clave externa. En la recreación, habrá un error si algo está mal.

Answer 5

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;

Puede usar este procedimiento almacenado para verificar que todas las bases de datos no tengan claves foráneas inválidas El resultado se cargará en la tabla INVALID_FOREIGN_KEYS . Parámetros de ANALYZE_INVALID_FOREIGN_KEYS :

1. Patrón de nombre de base de datos (estilo LIKE)
2. Patrón de nombre de tabla (estilo LIKE)

3. Si el resultado será temporal. Puede ser: ''Y'' , ''N'' , NULL .

   • En el caso de ''Y'' la tabla de resultados ANALYZE_INVALID_FOREIGN_KEYS será una tabla temporal. La tabla temporal no será visible para otras sesiones. Puede ejecutar varios procedimientos almacenados ANALYZE_INVALID_FOREIGN_KEYS(...) en paralelo con la tabla de resultados temporal.
   • Pero si está interesado en el resultado parcial de otra sesión, debe usar ''N'' , luego ejecutar SELECT * FROM INVALID_FOREIGN_KEYS; de otra sesión.

   • Debe usar NULL para omitir la creación de la tabla de resultados en la transacción, ya que MySQL ejecuta un compromiso implícito en la transacción para CREATE TABLE ... y DROP TABLE ... , por lo que la creación de la tabla de resultados causaría problemas en la transacción. En este caso, debe crear la tabla de resultados fuera de BEGIN; COMMIT/ROLLBACK; BEGIN; COMMIT/ROLLBACK; bloquear:

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

     Visite el sitio MySQL sobre el compromiso implícito: http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

Las filas INVALID_FOREIGN_KEYS contendrán solo el nombre de la base de datos, tabla y columna no válidos. Pero puede ver las filas de referencia no válidas con la ejecución del valor de la columna INVALID_FOREIGN_KEYS de INVALID_FOREIGN_KEYS si hay alguna.

Este procedimiento almacenado será muy rápido si hay índices en las columnas de referencia (también conocido como índice extranjero) y en las columnas de referencia (generalmente clave principal).