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