Comment trouver toutes les tables qui ont les clés étrangères qui font référence à table en particulier.colonne et ont les valeurs de ces clés étrangères?

J'ai une table dont la clé primaire est référencé dans plusieurs autres tableaux de 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 des tables Y et Z. Est-il une requête SQL que je peux utiliser pour le retour:

  1. Une liste de tables qui ont des clés étrangères dans X
  2. ET qui de ces tables ont effectivement des valeurs dans la clé étrangère
InformationsquelleAutor | 2009-04-30
  1. 372

    Ici, vous allez:

    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 les mêmes tables de noms de colonne vous pouvez également 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';
    • Cela pourrait-il être fait de manière récursive?
    • Merci beaucoup! Très utile...
    • SELECT TABLE_NAME plus mieux, puis SÉLECTIONNEZ *
    • Pour quel type de DB est-ce utile? MySql?
    • La question est étiqueté comme une base de données MySQL question.
    InformationsquelleAutor Alex N.
  2. 58

    MySQL 5.5 Manuel De Référence: "InnoDB et les Contraintes de CLÉ ÉTRANGÈRE"

    SELECT
  ke.REFERENCED_TABLE_SCHEMA parentSchema,
  ke.referenced_table_name parentTable,
  ke.REFERENCED_COLUMN_NAME parentColumnName,
  ke.TABLE_SCHEMA ChildSchema,
  ke.table_name childTable,
  ke.COLUMN_NAME ChildColumnName
FROM
  information_schema.KEY_COLUMN_USAGE ke
WHERE
  ke.referenced_table_name IS NOT NULL
  AND ke.REFERENCED_COLUMN_NAME = 'ci_id' ## Find Foreign Keys linked to this Primary Key
ORDER BY
  ke.referenced_table_name;
    InformationsquelleAutor Ovidiu
  3. 20

    Cette solution ne sera pas seulement d'afficher toutes les relations, mais aussi le nom de la contrainte, qui est nécessaire dans certains cas (par exemple baisse de 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 souhaitez vérifier les tables dans une base de données spécifique, ajoutez la ligne suivante:

    AND table_schema = 'database_name';
    InformationsquelleAutor Panayotis
  4. 14

    Vous pouvez trouver toutes schéma de l'information connexe dans le judicieusement nommé information_schema table.

    Vous pouvez consulter le tableau REFERENTIAL_CONSTRAINTS et KEY_COLUMN_USAGE. L'ancien vous indique les tables référencées par d'autres; ce dernier va vous dire comment leurs champs sont liés.

    InformationsquelleAutor Seb
  5. 4

    Liste de toutes les clés étrangères dans une db, y compris la description

        SELECT  
    i1.CONSTRAINT_NAME, i1.TABLE_NAME,i1.COLUMN_NAME,
    i1.REFERENCED_TABLE_SCHEMA,i1.REFERENCED_TABLE_NAME, i1.REFERENCED_COLUMN_NAME,
    i2.UPDATE_RULE, i2.DELETE_RULE 
    FROM   
    information_schema.KEY_COLUMN_USAGE AS i1  
    INNER JOIN 
    information_schema.REFERENTIAL_CONSTRAINTS AS i2 
    ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
    WHERE i1.REFERENCED_TABLE_NAME IS NOT NULL  
    AND  i1.TABLE_SCHEMA  ='db_name';

    limitant à une colonne dans une table de la table de 

    AND i1.table_name = 'target_tb_name' AND i1.column_name = 'target_col_name'
    InformationsquelleAutor bortunac
  6. 3

    J'ai écrit un petit bash onliner que vous pouvez écrire un script pour obtenir une sympathique sortie:

    mysql_references_to:

    mysql -uUSER -pPASS -A DB_NAME -se "USE information_schema; SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = '$1' AND REFERENCED_COLUMN_NAME = 'id'\G" | sed 's/^[ \t]*//;s/[ \t]*$//' |egrep "\<TABLE_NAME|\<COLUMN_NAME" |sed 's/TABLE_NAME: /./g' |sed 's/COLUMN_NAME: //g' | paste -sd "," -| tr '.' '\n' |sed 's/,$//' |sed 's/,/./'

    Pour que l'exécution: mysql_references_to transaccion (où transaccion aléatoire nom de la table) donne une sortie comme celle-ci:

    carrito_transaccion.transaccion_id
comanda_detalle.transaccion_id
comanda_detalle_devolucion.transaccion_positiva_id
comanda_detalle_devolucion.transaccion_negativa_id
comanda_transaccion.transaccion_id
cuenta_operacion.transaccion_id
...
    InformationsquelleAutor jhvaras
  7. -4

    Plus simple:

    1. Ouvrez phpMyAdmin

    2. Sur la gauche, cliquez sur nom de base de données

    3. Dans le coin supérieur droit de trouver "Concepteur" de l'onglet

    Toutes les contraintes seront indiqués.

    InformationsquelleAutor Mantas D