Wie kann man alle Tabellen finden, die Fremdschlüssel haben, die auf eine bestimmte Tabellenspalte verweisen und Werte für diese Fremdschlüssel haben?

Question

Ich habe eine Tabelle, deren Primärschlüssel in mehreren anderen Tabellen als Fremdschlüssel referenziert wird. Zum Beispiel:

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

Nun weiß ich nicht, wie viele Tabellen es in der Datenbank gibt, die Fremdschlüssel zu X enthalten, wie die Tabellen Y und Z. Gibt es eine SQL-Abfrage, die ich verwenden kann, um zurückzugeben:

1. Eine Liste von Tabellen, die Fremdschlüssel zu X haben
2. UND welche dieser Tabellen tatsächlich Werte im Fremdschlüssel haben

Answer 1

Hier ist es:

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

Wenn Sie mehrere Datenbanken mit ähnlichen Tabellen/Spaltennamen haben, können Sie Ihre Abfrage auch auf eine bestimmte Datenbank beschränken:

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

Answer 2

MySQL 5.5 Referenzhandbuch: "InnoDB und FOREIGN KEY-Einschränkungen"

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;

Answer 3

Bei dieser Lösung werden nicht nur alle Beziehungen, sondern auch der Name der Einschränkung angezeigt, was in einigen Fällen erforderlich ist (z. B. Drop Constraint):

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;

Wenn Sie Tabellen in einer bestimmten Datenbank prüfen wollen, fügen Sie Folgendes hinzu:

AND table_schema = 'database_name';

Answer 4

Sie finden alle schemabezogenen Informationen in der weise benannten information_schema Tisch.

Sie können die Tabelle überprüfen REFERENTIAL_CONSTRAINTS y KEY_COLUMN_USAGE . Erstere gibt Auskunft darüber, welche Tabellen von anderen referenziert werden, letztere gibt Auskunft darüber, wie die Felder miteinander in Beziehung stehen.

Answer 5

Auflistung aller Fremdschlüssel in einer Datenbank einschließlich Beschreibung

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

Einschränkung auf eine bestimmte Spalte in einer Tabelle Tabelle

AND i1.table_name = 'target_tb_name' AND i1.column_name = 'target_col_name'