Query to Find Foreign Key Relations
SELECT
TABLE_NAME AS child_table,
COLUMN_NAME AS child_column,
CONSTRAINT_NAME,
REFERENCED_TABLE_NAME AS parent_table,
REFERENCED_COLUMN_NAME AS parent_column
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
AND REFERENCED_TABLE_NAME IS NOT NULL
AND REFERENCED_TABLE_NAME = 'users';
Explanation
Retrieve Foreign Key Information:
TABLE_NAME → The child table containing the foreign key.COLUMN_NAME → The foreign key column in the child table.CONSTRAINT_NAME → The name of the foreign key constraint.REFERENCED_TABLE_NAME → The referenced table (users).REFERENCED_COLUMN_NAME → The primary key column in the users table.
Filter by Current Database:
TABLE_SCHEMA = DATABASE() ensures results are from the active database.
Filter by Foreign Key Relationships:
REFERENCED_TABLE_NAME IS NOT NULL ensures only foreign keys are included.REFERENCED_TABLE_NAME = 'users' restricts results to references targeting the users table.
Use Case
- Identify all tables that reference the
users table.
- Ensure referential integrity before altering or deleting records in
users.
- Understand database dependencies for schema modifications.
