andrewlocatelliwoodcock

Thoughts on Software

In mySQL, how do you find all tables that have foreign key constraints against another table

with one comment


The situation is that I need to know which tables in a database hold a foreign key constraint against a particular table, let’s call it TableX. The reason I need to know this is because I am planning to rename and retire TableX and in mySQL at least, the foreign key contraints follow the table rename. So really, I’m looking for metadata about my database and searching it for references to TableX.

It turns out that this is actually pretty simple:

USE information_schema;
SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'TableX' AND TABLE_SCHEMA='[my database name]';

This query will give you all the foreign keys in the named database that reference TableX. To find all foreign keys in the database is even simpler (although I can’t think of a use case for it at the moment):

USE information_schema;
SELECT * FROM KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='bigfishgames';

Simples. Once you know how …

Written by andrewlocatelliwoodcock

May 8, 2012 at 21:53

Posted in Databases, mySQL, SQL

Tagged with , ,

One Response

Subscribe to comments with RSS.

  1. Useful info. Lucky me I found your web site by chance, and I am surprised why this twist of fate didn’t happened earlier! I bookmarked it.

    zur Seite

    June 27, 2012 at 09:10


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: