How to View Foreign Key Constraints in MySQL?-Mysql Tutorial-php.cn

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

Copy after login

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

Copy after login

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!

source：php.cn

Previous article：How Can I Efficiently Find the Third or Nth Maximum Salary in a SQL Database? Next article：How to Efficiently Retrieve the Last Row for Each Unique Identifier in PostgreSQL?

Statement of this Website

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