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

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

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

---

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

---

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 "\<TABLE_NAME|\<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
...

---

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.