How to Find Tables with Foreign Keys Referencing a Specific Table.Column and Containing Values?

Retrieving Tables with Foreign Keys Referencing a Specific Table.Column and Containing Values

In the context of relational databases, it's often necessary to identify tables that possess foreign keys referencing a particular table.column combination, especially when the number of tables in the database is unknown. To achieve this, we can utilize the following query:

SELECT *
FROM
  information_schema.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'X'
  AND REFERENCED_COLUMN_NAME = 'X_id';

This query leverages the KEY_COLUMN_USAGE table in the information_schema database. It returns a list of tables that have foreign keys referencing the X table's X_id column. To ensure that only tables with populated foreign key values are retrieved, we can filter the results further by adding an additional condition:

SELECT *
FROM
  information_schema.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'X'
  AND REFERENCED_COLUMN_NAME = 'X_id'
  AND NOT ISNULL(TABLE_NAME);

This revised query eliminates tables with null values in their foreign key columns, providing a comprehensive list of tables that have both foreign keys referencing X.X_id and non-null values in those foreign keys.

The above is the detailed content of How to Find Tables with Foreign Keys Referencing a Specific Table.Column and Containing Values?. For more information, please follow other related articles on the PHP Chinese website!