DEV Community

Cover image for MariaDB Quick-tip #8 - All foreign key constraints
Allan Simonsen
Allan Simonsen

Posted on

2 1

MariaDB Quick-tip #8 - All foreign key constraints

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

All foreign key constraints

A well designed database relates its tables to each other using foreign key relations in order to ensure data integrity. But when the database has hundreds or even thousands of tables it can be a huge challenge finding all the tables related to a specific table you may have to modify.
The query below will find all the foreign key constraints between columns with a specific column name.

SET @DatabaseName := 'test_db';
SET @ColumnName := 'dept_no';

SELECT concat(rc.unique_constraint_schema, '.', rc.referenced_table_name) AS `Primary table`,
       concat(rc.constraint_schema, '.', rc.table_name) AS `Foreign table`, 
       rc.constraint_name AS `Constraint name`
  FROM information_schema.referential_constraints rc
  JOIN information_schema.key_column_usage cu ON rc.constraint_schema = cu.table_schema 
       AND rc.table_name = cu.table_name
       AND rc.constraint_name = cu.constraint_name
 WHERE rc.constraint_schema = @DatabaseName
   AND cu.COLUMN_NAME = @ColumnName
 ORDER BY rc.constraint_schema,
       rc.table_name;
Enter fullscreen mode Exit fullscreen mode

DBeaver screenshot

Hostinger image

Get n8n VPS hosting 3x cheaper than a cloud solution

Get fast, easy, secure n8n VPS hosting from $4.99/mo at Hostinger. Automate any workflow using a pre-installed n8n application and no-code customization.

Start now

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay