Comment voir toutes les clés étrangères d'une table ou d'une colonne ?

Question

Dans MySQL, comment puis-je obtenir une liste de toutes les contraintes de clé étrangère pointant vers une table particulière ? une colonne particulière ? C'est la même chose que cette question Oracle mais pour MySQL.

Answer 1

Pour une table

select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where
REFERENCED_TABLE_NAME = '<table>';

pour une colonne, c'est la même chose mais on ajoute un and pour le REFERENCED_COLUMN_NAME.

Answer 2

Voici un bon moyen de trouver ces informations sans passer par le schéma d'information :

SHOW CREATE TABLE `<yourtable>`;

J'ai trouvé cette réponse ici : MySQL : commande show constraints on tables

J'avais besoin de ce moyen car je voulais voir comment le FK fonctionnait, plutôt que de simplement voir s'il existait ou non.

Answer 3

Si vous utilisez InnoDB et des FK définis, vous pouvez interroger la base de données information_schema, par exemple :

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'myschema'
AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'mytable';

Answer 4

Je poste sur une ancienne réponse pour ajouter quelques informations utiles.

J'ai eu un problème similaire, mais je voulais également voir le CONSTRAINT_TYPE ainsi que les noms des tables et des colonnes REFERENCEES. Ainsi,

1. Pour voir tous les FKs dans votre table :

   USE '<yourschema>';
   
   SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
   AND i.TABLE_SCHEMA = DATABASE()
   AND i.TABLE_NAME = '<yourtable>';

2. Pour voir toutes les tables et les FKs de votre schéma :

   USE '<yourschema>';
   
   SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' 
   AND i.TABLE_SCHEMA = DATABASE();

3. Pour voir tous les FKs dans votre base de données :

   SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME 
   FROM information_schema.TABLE_CONSTRAINTS i 
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME 
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY';

Rappelez-vous !

Ce système utilise le moteur de stockage InnoDB. Si vous ne parvenez pas à faire apparaître les clés étrangères après les avoir ajoutées, c'est probablement parce que vos tables utilisent MyISAM.

Pour vérifier :

SELECT * TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<yourschema>';

Pour réparer, utilisez ceci :

ALTER TABLE `<yourtable>` ENGINE=InnoDB;

Answer 5

La façon la plus simple de voir toutes les clés étrangères

SHOW CREATE TABLE table_name;

Ceci montrera tout le détail de la table nommée tbl_name .