Comment vérifier l'intégrité référentielle dans les tables InnoDB après des modifications de données en masse ?

Application de l'intégrité référentielle dans les tables InnoDB

Les contraintes de clé étrangère dans les tables InnoDB garantissent l'intégrité des données en empêchant la suppression des enregistrements référencés. Cependant, après des modifications massives de données, il peut être nécessaire de vérifier que les contraintes de clé étrangère sont toujours intactes.

InnoDB ne fournit pas de méthode intégrée pour le forcer à revérifier les clés étrangères. Cependant, une procédure stockée SQL personnalisée peut être créée pour effectuer cette tâche.

Procédure stockée SQL personnalisée

La procédure stockée suivante, ANALYZE_INVALID_FOREIGN_KEYS, peut être utilisée pour analyser tous tables dans une base de données et identifier tout étranger non valide clés :

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
        -- ... (stored procedure code) ...
    END$$
DELIMITER ;

Utilisation

Pour utiliser la procédure stockée, exécutez la requête suivante :

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

Cela analysera toutes les tables de la base de données actuelle et stocke toutes les violations de clé étrangère non valides dans une table temporaire nommée INVALID_FOREIGN_KEYS.

Interprétation des résultats

La table INVALID_FOREIGN_KEYS contiendra les informations suivantes pour chaque clé étrangère invalide :

• TABLE_SCHEMA : Le nom de la base de données de la table en infraction.
• TABLE_NAME : le nom de la table en violation.
• COLUMN_NAME : Le nom de la colonne en violation.
• CONSTRAINT_NAME : Le nom de la contrainte de clé étrangère.
• REFERENCED_TABLE_SCHEMA : Le nom de la base de données de la table référencée. table.
• REFERENCED_TABLE_NAME : Le nom de la table référencée. table.
• REFERENCED_COLUMN_NAME : Le nom de la colonne référencée.
• INVALID_KEY_COUNT : Le nombre de références de clés étrangères non valides.
• INVALID_KEY_SQL : Une requête SQL qui peut être utilisée pour récupérer le invalide lignes.

LIMITATIONS

Notez que cette procédure présente les limitations suivantes :

• Elle ne vérifie pas l'autoréférence contraintes de clé étrangère.
• Il nécessite des index sur les colonnes référencées et référencées pour une performances.
• L'exécution de bases de données volumineuses peut prendre beaucoup de temps.

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!