Find foreign keys for a table

-- Portable: works on Postgres / MySQL / SQL Server / Snowflake / BigQuery.
SELECT
  rc.constraint_name,
  kcu.table_schema   AS source_schema,
  kcu.table_name     AS source_table,
  kcu.column_name    AS source_column,
  ccu.table_schema   AS target_schema,
  ccu.table_name     AS target_table,
  ccu.column_name    AS target_column,
  rc.update_rule,
  rc.delete_rule
FROM   information_schema.referential_constraints rc
JOIN   information_schema.key_column_usage kcu
  ON   rc.constraint_name   = kcu.constraint_name
JOIN   information_schema.constraint_column_usage ccu
  ON   rc.unique_constraint_name = ccu.constraint_name
WHERE  kcu.table_name = 'orders';                      -- the child / source table

-- Postgres shortcut: \d orders shows FKs inline.
\d orders

-- Find tables that reference a given parent (the "what cascades from this?" query).
SELECT
  kcu.table_name        AS child_table,
  kcu.column_name       AS child_column,
  rc.delete_rule
FROM   information_schema.referential_constraints rc
JOIN   information_schema.key_column_usage kcu
  ON   rc.constraint_name = kcu.constraint_name
JOIN   information_schema.constraint_column_usage ccu
  ON   rc.unique_constraint_name = ccu.constraint_name
WHERE  ccu.table_name = 'users';                       -- parent table

-- Postgres: full FK details using pg_catalog (richer, faster).
SELECT
  conrelid::regclass        AS child_table,
  a.attname                  AS child_column,
  confrelid::regclass        AS parent_table,
  af.attname                 AS parent_column,
  confdeltype                AS on_delete,
  confupdtype                AS on_update
FROM   pg_constraint c
JOIN   pg_attribute  a  ON a.attrelid  = c.conrelid AND a.attnum = ANY(c.conkey)
JOIN   pg_attribute  af ON af.attrelid = c.confrelid AND af.attnum = ANY(c.confkey)
WHERE  c.contype = 'f';

-- SQL Server.
SELECT
  OBJECT_NAME(parent_object_id)             AS child_table,
  OBJECT_NAME(referenced_object_id)         AS parent_table,
  name                                      AS constraint_name
FROM   sys.foreign_keys;