Chris on MySQL

The other day, I was troubleshooting a deadlock, and I wondered if any of the table’s columns were referenced by any foreign keys (fks) from any other tables in the instance.

Well, this is actually very simple with information_schema (I_S):

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME='parent';

Where `parent` is the name of the table you’re searching for.

Note this query does not restrict on the database, or schema, name, but that could easily be added (or any other number of conditions). Here is an example where I only return the most useful columns (which could be useful for determining said conditions):

SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA,
	TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA,
	REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME='parent';

If there are any fks, this will return something like:

+-------------------+-----------------+--------------+------------+-------------+
| CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |
+-------------------+-----------------+--------------+------------+-------------+
| test              | child_ibfk_1    | test         | child      | parent_id   |
+-------------------+-----------------+--------------+------------+-------------+

+-------------------------+-----------------------+------------------------+
| REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-------------------------+-----------------------+------------------------+
| test                    | parent                | id                     |
+-------------------------+-----------------------+------------------------+

Hope this helps.

Tags: deadlock, deadlock detection, foreign key, information schema, innodb deadlock, innodb foreign keys, mariadb deadlock, mariadb foreign keys, mysql deadlock, mysql foreign keys, mysql information schema, referenced column, referenced table

This entry was posted on Wednesday, March 21st, 2012 at 10:53 am and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.