MySQL Key Constraints (FK) by Database

Luke Hebert

- 2 minutes read - 282 words

When you inherit a MySQL database or need to audit its schema, knowing which tables are linked by foreign keys saves a lot of guesswork. The query below pulls every foreign key constraint in a specific schema and shows you exactly which column references which, so you can map dependencies at a glance.

Finding All Foreign Key Constraints

Run the following query against the information_schema database. Replace scm with the name of the schema you want to inspect:

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
  AND table_schema = 'scm';

What the Query Returns

  • foreign key: The table and column that holds the foreign key value.
  • references: The table and column that the foreign key points to (the parent).
  • constraint name: The name MySQL assigned to the constraint, useful when you need to drop or alter it later.

A Few Things to Keep in Mind

  • The KEY_COLUMN_USAGE table lists one row per column in a composite foreign key, so a two-column FK will appear as two rows sharing the same constraint_name.
  • If you also want to see the ON DELETE and ON UPDATE rules, join to information_schema.REFERENTIAL_CONSTRAINTS on constraint_name and table_schema.
  • You need SELECT privileges on the target schema to read its information_schema rows.

List every table in the schema that has at least one foreign key:

SELECT DISTINCT table_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE referenced_table_name IS NOT NULL
  AND table_schema = 'scm';

Drop a specific foreign key constraint (use with care):

ALTER TABLE scm.orders
  DROP FOREIGN KEY fk_orders_customer;

These snippets should cover most day-to-day FK auditing tasks without needing a GUI tool.