How Can I Identify Foreign Key Constraints in MySQL?

Identifying Foreign Key Constraints in MySQL

In database management, understanding foreign key relationships is crucial. This question addresses the specific task of retrieving all foreign key constraints associated with a particular table or column in MySQL.

Viewing Foreign Keys for a Table

To obtain a list of foreign key constraints referencing a specific table, execute the following query:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = (SELECT DATABASE()) AND
  REFERENCED_TABLE_NAME = '<table_name>' \G

Replace with the name of the target table.

Viewing Foreign Keys for a Column

To retrieve foreign key constraints referencing a particular column, use this modified query:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = (SELECT DATABASE()) AND
  REFERENCED_TABLE_NAME = '<table_name>' AND
  REFERENCED_COLUMN_NAME = '<column_name>' \G

Replace with the name of the target table and with the name of the target column.

The above is the detailed content of How Can I Identify Foreign Key Constraints in MySQL?. For more information, please follow other related articles on the PHP Chinese website!