Force InnoDB de vérification des clés étrangères sur une table ou des tables?

J'ai un ensemble de InnoDB tables que j'ai régulièrement besoin de maintenir par la suppression de certaines lignes et en insérant les autres. Plusieurs tables ont des contraintes de clé étrangère référence à d'autres tables, donc cela signifie que la table de chargement de l'ordre est important. Pour insérer les nouvelles lignes, sans se soucier de l'ordre des tables, j'utilise:

SET FOREIGN_KEY_CHECKS=0;

avant, et puis:

SET FOREIGN_KEY_CHECKS=1;

après.

Lorsque le chargement est terminé, j'aimerais vérifier que les données de la mise à jour des tables de toujours tenir l'intégrité référentielle--que les nouvelles lignes, ne pas briser les contraintes de clé étrangère-mais il semble qu'il n'y a aucun moyen de le faire.

Comme un test, j'ai entré les données que j'étais sûr violé les contraintes de clés étrangères, et sur la ré-activation de la clé étrangère chèques, mysql produit pas d'avertissements ou d'erreurs.

Si j'ai essayé de trouver un moyen de spécifier la table de chargement de l'ordre, de gauche et le contrôle des clefs étrangères sur pendant le processus de chargement, ce ne serait pas me permettre de charger des données dans une table qui dispose d'un auto-référencement contrainte de clé étrangère, de sorte que ce ne serait pas une solution acceptable.

Est-il de toute façon à force de InnoDB pour vérifier une table ou une base de données des contraintes de clés étrangères?

  • C'est une parodie, et incroyable, que MySQL n'effectue pas de FK de validation lorsque vous ré-activé FKs via SET FOREIGN_KEY_CHECKS=1;. À tout le moins, ils devraient exposer des fonctionnalités intégrées afin de ne pas avoir à sauter à travers des cerceaux pour valider l'intégrité des données dans la base de données.
InformationsquelleAutor pcronin | 2010-02-12
  1. 95
    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;

    Vous pouvez utiliser cette procédure pour vérifier la base de données non valide pour les clés étrangères.
    Le résultat sera chargé dans INVALID_FOREIGN_KEYS table.
    Les paramètres de ANALYZE_INVALID_FOREIGN_KEYS:

    1. De la base de données du modèle de nom (style)
    2. Nom de la Table de modèle (style)

    3. Si le résultat sera que temporaire. Il peut être: 'Y', 'N', NULL.

      • En cas de 'Y' la ANALYZE_INVALID_FOREIGN_KEYS résultat de la table sera la table temporaire.
        La table temporaire ne sera pas visible pour les autres séances.
        Vous pouvez exécuter plusieurs ANALYZE_INVALID_FOREIGN_KEYS(...) procédure stockée en parallèle avec résultat temporaire de la table.
      • Mais si vous êtes intéressé par le résultat partiel à partir d'une autre session, vous devez utiliser 'N', puis exécuter SELECT * FROM INVALID_FOREIGN_KEYS; à partir d'une autre session.

      • Vous devez utiliser NULL à sauter le résultat de la création de la table dans la transaction, parce que MySQL exécute commit implicite dans la transaction pour CREATE TABLE ... et DROP TABLE ..., de sorte que la création de la table de résultat serait la cause de problème dans la transaction. Dans ce cas, vous devez créer le tableau de résultat de BEGIN; COMMIT/ROLLBACK; bloc:

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

        Visiter le site de MySQL sur commit implicite: http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

    La INVALID_FOREIGN_KEYS lignes ne contiennent que le nom de la base de données non valide, la table, la colonne. Mais vous pouvez voir les invalides se référant lignes avec l'exécution de la valeur de INVALID_KEY_SQL colonne de INVALID_FOREIGN_KEYS si il y en a.

    Cette procédure stockée sera très rapide si il y a des indices sur le renvoi des colonnes (aka. étrangères de l'indice) et sur les visées des colonnes (généralement la clé primaire).

    • Grande réponse. Bienvenue à Débordement de Pile!
    • J'ai trouvé que je devais ajouter 'collate utf8_general_ci" après la checked_* les paramètres de l'curseurs clause where parce que mes informations de schéma utilise utf8_general_ci où que le compte de connexion utilise le plus correct utf8_unicode_ci. Sans cela, j'ai reçu un classement erreur d'incompatibilité sur la clause like. "OÙ CONSTRAINT_SCHEMA COMME checked_database_name collate utf8_general_ci ET TABLE_NAME COMME checked_table_name collate utf8_general_ci ET REFERENCED_TABLE_SCHEMA n'EST PAS NULLE;
    • On peut se débarrasser de la LIKE assemblage d'erreur par l'application à l'aide de la même classements sur les deux côtés comme par exemple: (CONSTRAINT_SCHEMA COLLATE utf8_unicode_ci) LIKE (checked_database_name COLLATE utf8_unicode_ci).
    • Notez que l'on peut ajouter AND REFERENCED_TABLE_NAME = 'name_of_the_table_that_one_wants_to_check_children_for' à foreign_key_cursor 's WHERE pour la vérification d'un seul tableau, les enfants de FKs.
    InformationsquelleAutor mattty
  2. 16

    Merci pour cette excellente réponse - c'est un outil très pratique. Voici une version légèrement modifiée de la procédure qui inclut SQL dans la table de sortie pour supprimer les clés invalides clés à portée de main pour le cas où vous avez confirmé que ces lignes sont simplement des orphelins de manquant/désactivé delete cascade règles (et pas des orphelins de clé primaire changements ou d'autres cas plus complexes).

    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;
    • Je sais que c'est vieux maintenant mais suis tombé sur ce sujet et je me demandais: une raison quelconque vous n'êtes pas à l'aide de CONCAT('DELETE REFERRING ', @from_part, ';') pour l'instruction DELETE?
    InformationsquelleAutor
  3. 1

    J'ai modifié le script pour gérer plusieurs colonnes de clés étrangères.

    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
    InformationsquelleAutor nailujed
  4. 1

    La même case, mais pour l'UNIQUE invalide clés de l'analyse:

    --> Petit bug/feature: Il fera rapport en double nuls aussi. (Alors que mysql permet de dupliquer les valeurs null).

    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;
    InformationsquelleAutor Marinos An
  5. 1

    Il n'y a pas d'outil, qui peut le faire. Mais vous pouvez écrire un script, qui va marcher à travers tous vos tables, de supprimer et recréer contraintes de clé étrangère. Pour les loisirs, il y aura une erreur si quelque chose est faux.

    InformationsquelleAutor Vladislav Rastrusny