How to View Foreign Key Constraints in MySQL?

Viewing Foreign Key Constraints in MySQL

Querying foreign key constraints in MySQL can provide valuable insights into data associations. This guide addresses the question of how to retrieve a comprehensive list of foreign key constraints referencing a specific table or column.

Identifying Foreign Keys for a Table

To list all foreign key constraints pointing to a particular 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

Identifying Foreign Keys for a Column

To focus on foreign keys referencing a specific column within a table, modify the query as follows:

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

In both queries, replace

with the name of the table you wish to inspect, and with the name of the column within that table.

By executing these queries, you can gain a comprehensive understanding of foreign key dependencies and data relationships within your MySQL database.

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

mysql for this column table database

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：How Can I Efficiently Find the Third or Nth Maximum Salary in a SQL Database?Next article：How Can I Efficiently Find the Third or Nth Maximum Salary in a SQL Database?

Related articles

See more

• MySQL Advanced 16 - Memory Optimization
• mysql left, right, inner join
• mysql null和' '
• mysql order by
• mysql having

Public welfare online PHP training，Help PHP learners grow quickly！

About usDisclaimerSitemap

© php.cn All rights reserved