Comment forcer les vérifications de clés étrangères dans les tables InnoDB lorsque `SET FOREIGN_KEY_CHECKS=1` ne parvient pas à déclencher des avertissements ou des erreurs ?

Comment appliquer les vérifications de clés étrangères dans les tables InnoDB

Problème

Vous gérez un ensemble de tables InnoDB qui subissent des mises à jour périodiques impliquant la suppression de lignes et l'insertion. Ces tables ont des contraintes de clé étrangère, ce qui rend l'ordre de chargement des tables crucial. Pour éviter les problèmes de contrainte de clé étrangère, vous comptez sur la désactivation et la réactivation des vérifications de clé étrangère (SET FOREIGN_KEY_CHECKS=0 et SET FOREIGN_KEY_CHECKS=1). Cependant, vous constatez que le chargement des données avec les vérifications désactivées ne déclenche aucun avertissement ou erreur lorsque vous les réactivez, malgré la violation intentionnelle des règles de clé étrangère.

Solution

InnoDB ne fournit pas un moyen direct de forcer une vérification de clé étrangère, mais il existe des solutions de contournement.

Utilisation d'une procédure stockée

Une approche consiste pour créer une procédure stockée (ANALYZE_INVALID_FOREIGN_KEYS) qui examine toutes les clés étrangères d'une base de données et identifie celles qui sont violées. La procédure parcourt les clés étrangères, génère des requêtes pour vérifier les références non valides et stocke les résultats dans une table temporaire.

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
        -- ... (procedure implementation)
    END$$

DELIMITER ;

Pour utiliser la procédure, fournissez trois paramètres :

1. Le modèle de nom de base de données (checked_database_name) à vérifier (par exemple, % pour toutes les bases de données)
2. Le modèle de nom de table (checked_table_name) pour vérifier (par exemple, % pour toutes les tables)
3. Si la table de résultats doit être temporaire (temporary_result_table) (Y pour temporaire, N pour permanent)

La procédure se charge les résultats dans une table temporaire ou permanente nommée INVALID_FOREIGN_KEYS. Cette table contient des informations sur les clés étrangères non valides, notamment le schéma de la table, le nom de la table, le nom de la colonne, le nom de la contrainte, le schéma de la table référencée, le nom de la table référencée, le nom de la colonne référencée, le nombre de clés non valides et une requête SQL pour récupérer les lignes non valides.

Exemple d'utilisation

Pour vérifier les clés étrangères invalides dans toutes les bases de données :

CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');

Pour afficher le résultats :

SELECT * FROM INVALID_FOREIGN_KEYS;

Pour récupérer les lignes invalides pour une clé étrangère spécifique, exécutez la requête INVALID_KEY_SQL correspondante à partir de la table INVALID_FOREIGN_KEYS.

Remarque

Cette approche nécessite des index sur les colonnes référentes et référencées pour des performances optimales.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!