Comment détecter et corriger les violations de clés étrangères dans les tables InnoDB ?

Détection des violations de clé étrangère dans les tables InnoDB

Lors de la manipulation de tables InnoDB avec des contraintes de clé étrangère, il est essentiel de vérifier l'intégrité des données après modifications. Cependant, la simple réactivation des vérifications de clé étrangère peut ne pas suffire à détecter les violations.

Configuration de la vérification de clé étrangère

Pour éviter les incohérences de données lors du chargement de la table, vous pouvez désactiver temporairement les vérifications de clé étrangère avant d'insérer nouvelles lignes et réactivez-les une fois le processus terminé :

SET FOREIGN_KEY_CHECKS=0;
-- Insert new rows
SET FOREIGN_KEY_CHECKS=1;

Vérification de la clé étrangère Intégrité

Cependant, après avoir réactivé les vérifications de clés étrangères, il peut encore y avoir des violations non détectées en raison de la nature différée de la vérification des contraintes dans InnoDB. Pour détecter et résoudre ces problèmes de manière proactive, vous pouvez utiliser la procédure stockée suivante :

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 DONE INT DEFAULT 0;
  -- Iterate over foreign keys
  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;

  -- Create temporary result table for invalid foreign keys
  IF temporary_result_table = 'Y' THEN
    DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
    CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(...);
  ELSE
    DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
    CREATE TABLE INVALID_FOREIGN_KEYS(...);
  END IF;

  -- Check foreign key violations and insert into result table
  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 = "...";
    SET @full_query = "...";
    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` = "...";
    END IF;
    DEALLOCATE PREPARE stmt; 
  END LOOP foreign_key_cursor_loop;
END$$

DELIMITER ;

Utilisation

Pour utiliser la procédure stockée, exécutez-la avec les noms de base de données et de table souhaités :

CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;

SELECT * FROM INVALID_FOREIGN_KEYS;

La table INVALID_FOREIGN_KEYS contiendra les détails de toutes les clés étrangères non valides, y compris les tables, colonnes référencées et référencées et une requête SQL qui peut être utilisé pour identifier les lignes spécifiques en infraction.

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!