🔍 How to Find Foreign Keys Referencing the users Table in MySQL

[MyrinNew]

🧩 The Real-World Problem

Imagine this:

A development team is handed a large, inherited MySQL database — over 100 tables, minimal documentation, and one critical task at hand:

Find all tables that are related to the users table so that deleting a user doesn’t leave behind orphaned records.

This often happens during data cleanup, GDPR compliance, or when building cascade delete workflows. Manually scanning through all migration files or schema definitions isn’t just tedious — it’s prone to errors and doesn’t scale.


So what’s a clean, reliable way to get this done?



💡 The SQL Query That Solves It

Here’s a powerful query that pinpoints all foreign key relationships targeting the id field of the users table:






SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = 'users'
AND REFERENCED_COLUMN_NAME = 'id';







✅ What This Query Does:

• Searches the entire database for foreign key relationships
• Filters results to only those that reference users.id
• Outputs exactly which table and column creates that link

📊 Sample Output

For example, if the database has tables like:

• posts with a user_id column
• comments with an author_id column
• orders with a user_id column

The output would resemble:

posts	user_id	fk_posts_user	users	id
comments	author_id	fk_comments_user	users	id
orders	user_id	fk_orders_user	users	id

This gives an instant overview of all tables referencing the users table — no guesswork needed.





🛠️ Use Case: Safe User Deletion Workflow

In many real-world applications, especially SaaS systems or e-commerce platforms, deleting a user must also clean up related data such as:

• Posts or content they created
• Orders they placed
• Comments or messages they wrote

Once the referencing tables are identified, developers can:

1. Apply ON DELETE CASCADE where logical and safe
2. Manually delete child records in the correct order
3. Audit and log user-related records before deletion for traceability

More...