How to find all tables that have foreign keys that reference particular table column and have values for those foreign keys

Question

I have a table whose primary key is referenced in several other tables as a foreign key   For example     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         Now  I don t know how many tables there are in the database that contain foreign keys into X like tables Y and Z  Is there a SQL query that I can use to return    A list of tables that have foreign keys into X AND which of those tables actually have values in the foreign key

User · Answer

MySQL 5 5 Reference Manual   InnoDB and FOREIGN KEY Constraints   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

User · Answer

Here you go   USE information schema  SELECT   FROM   KEY COLUMN USAGE WHERE   REFERENCED TABLE NAME    X    AND REFERENCED COLUMN NAME    X id     If you have multiple databases with similar tables column names you may also wish to limit your query to a particular database   SELECT   FROM   KEY COLUMN USAGE WHERE   REFERENCED TABLE NAME    X    AND REFERENCED COLUMN NAME    X id    AND TABLE SCHEMA    your database name

User · Answer

Listing all foreign keys in a db including 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     restricting to a specific column in a table table   AND i1 table name    target tb name  AND i1 column name    target col name

User · Answer

Easiest  1  Open phpMyAdmin 2  On the left click database name 3  On the top right corner find  Designer  tab  All constraints will be shown there

User · Answer

This solution will not only display all relations but also the constraint name  which is required in some cases  e g  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    If you want to check tables in a specific database  add the following   AND table schema    database name

User · Answer

You can find all schema related information in the wisely named information schema table   You might want to check the table REFERENTIAL CONSTRAINTS and KEY COLUMN USAGE  The former tells you which tables are referenced by others  the latter will tell you how their fields are related

User · Answer

I wrote a little bash onliner that you can write to a script to get a friendly output   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    lt TABLE NAME   lt COLUMN NAME   sed  s TABLE NAME     g   sed  s COLUMN NAME    g    paste -sd     -  tr       n   sed  s        sed  s         So the execution  mysql references to transaccion  where transaccion is a random table name  gives an output like this   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

[mysql] How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?

Examples related to mysql

Examples related to foreign-keys