CODEWITHSUNDEEP
mysqlphpmyadminforeign-keys
timetofly
timetofly

Reputation: 3077

How do I see which columns throughout my database reference one particular column as a foreign key? Like a "reverse" foreign key?

I've got a pretty big relational database and am working on the admin backend. I want to know which tables reference a column in one particular table.

For example: let's say I've got a table products with id as the primary index. I can have a lot of tables that reference this index, such as an orders table, a user_bookmarks table, and a product_reviews table. If I want to delete a particular product, I first need to do some work with the other tables--a simple "cascade" or "delete" directive wouldn't be enough. How do I get MySQL to tell me exactly which columns in which tables are referencing the products.id index?

Bonus question: is there a built-in way to get this info using phpmyadmin?

Upvotes: 1

Views: 60

Answers (2)

Steven Scott
Steven Scott

Reputation: 11250

Could you not read the Schema from the database into MySQL Workbench, and then use the tool to plot out the relationships between the tables? I have not tried this myself, as I do the design in MySQL Workbench and then the mappings, etc... and then export to MySQL to create the database.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562368

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE (REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME) = ('mydatabase', 'products')

Re your comment:

MySQL doesn't support recursive queries, so unfortunately there's no way to find the complete graph of foreign keys in a single query. The best you could do is for each of the results of the above query, run the query again, substituting the TABLE_SCHEMA and TABLE_NAME as the string constants in the WHERE clause (be careful not to enter into infinite loops if you have circular foreign keys).

Upvotes: 1

Related Questions