How to list table foreign keys

sql

foreign-keys

postgresql

mysql

byNikita Barsukov·Sep 20, 2024

To list all foreign keys stored in a database, we will traverse the information_schema. Here's a SQL Server example outlining the process:

SELECT 
  fk.name AS FK_name, // Self-explanatory, akin to your nickname on Twitter! 
  tp.name AS parent_table, // Haha, parent table, where all the data yelling happens!
  cp.name AS parent_column, // Columns: the supporting pillars in the world of tables!
  tr.name AS referenced_table, // Oh, look! A relationship indicator!
  cr.name AS referenced_column // Because each detail counts.
FROM 
  sys.foreign_keys AS fk
  INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
  INNER JOIN sys.tables AS tp ON fk.parent_object_id = tp.object_id
  INNER JOIN sys.columns AS cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
  INNER JOIN sys.tables AS tr ON fk.referenced_object_id = tr.object_id
  INNER JOIN sys.columns AS cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id;

Congratulations! You've just received a parcel carrying some fancy details, namely foreign key names, parent tables and columns, and referenced tables and columns.

Diving deeper

Different RDBMS have their own quirks when dealing with foreign keys. Here are some situation-specific examples:

PostgreSQL synthesis

In PostgreSQL, tap into pg_catalog for some hidden gems:

SELECT 
  conname AS constraint_name, // Let's find some constraints to break!
  (SELECT relname FROM pg_class WHERE oid = conrelid) AS table_name, // Let's reveal some secret identities!
  attname AS column_name // All powerful columns unite!
FROM 
  pg_constraint, 
  LATERAL unnest(conkey) AS myColumn(attNum), //Unleash the unnesting powers! Even Power Rangers would be jealous.
  LATERAL (SELECT attname FROM pg_attribute WHERE attrelid = conrelid AND attnum = myColumn.attNum) AS myColName
WHERE 
  contype = 'f' // Say hello to all foreign keys!
  AND conrelid = 'your_table_name'::regclass; // Select your table as if picking for a super league!

PS: It's "ley" not "lie" in "foreign_key"!

MySQL magnifying glass

MySQL users aren't left out either:

SELECT 
  TABLE_NAME, // Show yourself!
  COLUMN_NAME, // Reveal thy name!
  CONSTRAINT_NAME, // You too, Constraint!
  REFERENCED_TABLE_NAME, // UNO reverse card in action!
  REFERENCED_COLUMN_NAME // Let's keep this relationship going!
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
  REFERENCED_TABLE_SCHEMA = 'your_database_name' // Your very own database name tag!
  AND TABLE_NAME = 'your_table_name'; // Because you should always name your tables!

SQLite sleuthing

For SQLite, PRAGMA is your best bet:

PRAGMA foreign_key_list('your_table_name'); // PRAGMA: the secret handshake of SQLite!

Examining multicolumn foreign key

Multicolumn foreign keys pose an interesting challenge. Below is a PostgreSQL solution:

SELECT 
  conname,
  conrelid::regclass,
  unnest(conkey) AS conkey_unnest // Like splitting an array into yummy, bite-sized morsels! 
FROM 
  pg_constraint
WHERE 
  contype = 'f';  // The Scarlet Letter of constraints!

Advanced goodies

Shortcut to foreign keys

Aggregated data improves efficiency. In PostgreSQL, a view for quick access with SQL looks like:

CREATE OR REPLACE VIEW foreign_keys_view AS
... // Just an amazing congregating point for all foreign keys.

Handling name conflicts

Fully qualified names are your best friends when names get confusing in PostgreSQL.

Unleashing Postgres extensions

For serious advanced querying capabilities, consider exploring Postgres extensions like adminpack.

explain-codes / Sql / How to list table foreign keys

Linked

Postgres: How to do Composite keys?



Can't drop table: A foreign key constraint fails



How to find a table having a specific column in postgresql



How do I drop a foreign key constraint only if it exists in sql server?



Primary key/foreign Key naming convention



Difference between RESTRICT and NO ACTION



How to Store a List in a DB Column



Diving deeper Advanced goodies