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

Admin 1
Viewed 182461

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:

  1. A list of tables that have foreign keys into X
  2. AND which of those tables actually have values in the foreign key
foreign-keys mysql
9 Answers

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_902680

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_765830

06 2022

Based on @Panayotis answer but with better structure.

This will list out all the constrains from multiple tables.
I have included the TABLE_SCHEMA to show the database name too.

SQL

SELECT
    TABLE_SCHEMA AS 'Database',
    TABLE_NAME AS t1,
    REFERENCED_TABLE_NAME AS 't2 (reference table)',
    COLUMN_NAME AS 't1 column',
    REFERENCED_COLUMN_NAME AS 't2 column (reference table)',
    CONSTRAINT_NAME AS 't1 (constrain name)'
FROM
    information_schema.key_column_usage
WHERE
    referenced_table_name IS NOT NULL

Output

+------------+------------+---------------------+---------------+----------------------------+------------------------+
| Database   | t1         | t2 (reference table) | t1 column     | t2 column (reference table) | t1 (constrain name)    |
+============+============+=====================+===============+============================+========================+
| foobar     | credential | userdetail          | userdetail_fk | id                         | credentialUserdetailFk |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
| foobar     | loginlog   | userdetail          | userdetail_fk | id                         | loginlogUserdetailFk   |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
+ client     | userdetail | client              | client_fk     | id                         | userdetailClientFk     |
+------------+------------+---------------------+---------------+----------------------------+------------------------+
user_54132830

SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA LIKE 'your_database' AND TABLE_SCHEMA LIKE 'your_database' AND REFERENCED_TABLE_SCHEMA LIKE 'your_database' AND REFERENCED_TABLE_NAME LIKE 'your_table' AND REFERENCED_COLUMN_NAME LIKE 'your_column';

user_155342760
Related