MySQL : Comment puis-je trouver toutes les tables qui ont des clés étrangères qui font référence à une colonne particulière de la table ET qui ont des valeurs pour ces clés étrangères ?

Question

J'ai une table dont la clé primaire est référencée dans plusieurs autres tables en tant que clé étrangère. Par exemple :

  CREATE TABLE `X` (
    `X_id` int NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY  (`X_id`)
  )
  CREATE TABLE `Y` (
    `Y_id` int(11) NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    `X_id` int DEFAULT NULL,
    PRIMARY KEY  (`Y_id`),
    CONSTRAINT `Y_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
  )
  CREATE TABLE `Z` (
    `Z_id` int(11) NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    `X_id` int DEFAULT NULL,
    PRIMARY KEY  (`Z_id`),
    CONSTRAINT `Z_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
  )

Maintenant, je ne sais pas combien de tables il y a dans la base de données qui contiennent des clés étrangères dans X comme les tables Y et Z. Existe-t-il une requête SQL que je peux utiliser pour retourner : 1. Une liste des tables qui ont des clés étrangères dans X ET 2. lesquelles de ces tables ont effectivement des valeurs dans la clé étrangère.

Merci !

Answer 1

Voilà :

USE information_schema;
SELECT *
FROM
  KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'X'
  AND REFERENCED_COLUMN_NAME = 'X_id';

Si vous avez plusieurs bases de données avec des noms de tables/colonnes similaires, vous pouvez également souhaiter limiter votre requête à une base de données particulière :

SELECT *
FROM
  KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'X'
  AND REFERENCED_COLUMN_NAME = 'X_id'
  AND TABLE_SCHEMA = 'your_database_name';

Answer 2

Manuel de référence de MySQL 5.5 : "InnoDB et les contraintes FOREIGN KEY".

SELECT
  ke.referenced_table_name parent,
  ke.table_name child,
  ke.constraint_name
FROM
  information_schema.KEY_COLUMN_USAGE ke
WHERE
  ke.referenced_table_name IS NOT NULL
ORDER BY
  ke.referenced_table_name;

Answer 3

Cette solution permet d'afficher non seulement toutes les relations mais aussi le nom de la contrainte, ce qui est nécessaire dans certains cas (par exemple, supprimer une contrainte) :

select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;

Si vous voulez vérifier les tables d'une base de données spécifique, ajoutez le nom de la table à la fin de la requête :

select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    constraint_name as 'constraint name'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'database_name';

Inspiré par ce post ici

Answer 4

Vous pouvez trouver toutes les informations relatives aux schémas dans le sagement nommé information_schema table.

Vous pouvez vérifier le tableau REFERENTIAL_CONSTRAINTS et KEY_COLUMN_USAGE . Le premier vous indique quelles tables sont référencées par d'autres ; le second vous dira comment leurs champs sont liés.

Answer 5

Essayez ça :

USE information_schema;

SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'table_name' AND 

REFERENCED_COLUMN_NAME = 'table_field';

Cela a fonctionné pour moi.