Write a query to list all foreign keys referencing a given table in a schema (engine-specific system tables allowed).

Answer

Foreign key relationships aren't visible in a normal data table - they're stored in the database's own metadata (system catalog), which needs an engine-specific query to inspect.

Solution (PostgreSQL / Information Schema Standard)

SELECT
  tc.constraint_name,
  tc.table_name AS referencing_table,
  kcu.column_name AS referencing_column,
  ccu.table_name AS referenced_table,
  ccu.column_name AS referenced_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND ccu.table_name = 'Customers';

Step by Step

1. information_schema.table_constraints lists every constraint in the database, filtered here to only FOREIGN KEY type constraints.
2. key_column_usage identifies which table and column each constraint actually lives on (the referencing side).
3. constraint_column_usage identifies what that constraint ultimately points to (the referenced side) - filtering this to 'Customers' finds every foreign key anywhere in the schema that references that specific table.

Engine-Specific Alternative: MySQL

SELECT table_name AS referencing_table, column_name AS referencing_column,
       referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name = 'Customers'
  AND table_schema = DATABASE();

MySQL's key_column_usage conveniently includes the referenced table/column directly in one table, unlike the standard three-table join required for full ANSI-compliant information_schema usage - a good example of why "engine-specific system tables allowed" is explicitly part of this question's wording.

Key Exam Traps

• There's no universal SQL syntax for this - metadata queries are inherently engine-specific, and stating that clearly (rather than presenting one dialect's syntax as if it were universal) shows real database experience.
• information_schema vs engine-native catalogs: Some engines (like PostgreSQL) also expose a richer native pg_catalog that some DBAs prefer over the more limited ANSI-standard information_schema - worth mentioning as an alternative source of the same information.
• Schema/database scoping: Forgetting to filter by the current schema or database (as the MySQL example does with table_schema = DATABASE()) can return foreign keys from unrelated schemas sharing the same table name, on multi-schema/multi-database servers.