Summary

If your FK report “misses” constraints, it’s not statistics. FK metadata lives in system catalogs and shows up regardless of ANALYZE. The usual culprits are schema filters, case sensitivity, partitions, NOT VALID constraints, permissions, or just being in the wrong place you expected. Below are dependable queries for Postgres and SQL Server, plus a quick checklist to troubleshoot gaps.

---

Part 1: Postgres

Quick wins

A. List all foreign keys in the database

SELECT
  n.nspname          AS table_schema,
  t.relname          AS table_name,
  c.conname          AS fk_name,
  rn.nspname         AS referenced_schema,
  rt.relname         AS referenced_table
FROM pg_constraint c
JOIN pg_class      t  ON t.oid  = c.conrelid
JOIN pg_namespace  n  ON n.oid  = t.relnamespace
JOIN pg_class      rt ON rt.oid = c.confrelid
JOIN pg_namespace  rn ON rn.oid = rt.relnamespace
WHERE c.contype = 'f'
ORDER BY table_schema, table_name, fk_name;

B. Same, but include column pairs in order

WITH fks AS (
  SELECT
    c.oid                                  AS fk_oid,
    n.nspname                              AS table_schema,
    t.relname                              AS table_name,
    c.conname                              AS fk_name,
    rn.nspname                             AS referenced_schema,
    rt.relname                             AS referenced_table,
    c.conkey                               AS con_cols,
    c.confkey                              AS ref_cols,
    c.convalidated                         AS is_validated
  FROM pg_constraint c
  JOIN pg_class t      ON t.oid  = c.conrelid
  JOIN pg_namespace n  ON n.oid  = t.relnamespace
  JOIN pg_class rt     ON rt.oid = c.confrelid
  JOIN pg_namespace rn ON rn.oid = rt.relnamespace
  WHERE c.contype = 'f'
),
cols AS (
  SELECT
    fk.fk_name,
    fk.table_schema,
    fk.table_name,
    fk.referenced_schema,
    fk.referenced_table,
    fk.is_validated,
    a.attname  AS column_name,
    ra.attname AS referenced_column_name,
    ord.ordinality
  FROM fks fk
  JOIN LATERAL unnest(fk.con_cols)  WITH ORDINALITY AS ord(attnum, ordinality)  ON TRUE
  JOIN LATERAL unnest(fk.ref_cols)  WITH ORDINALITY AS rord(attnum, ordinality) ON rord.ordinality = ord.ordinality
  JOIN pg_attribute a  ON a.attrelid = fk.fk_oid::regclass::oid AND a.attnum = ord.attnum
  JOIN pg_attribute ra ON ra.attrelid = fk.referenced_table::regclass::oid AND ra.attnum = rord.attnum
)
SELECT
  table_schema,
  table_name,
  fk_name,
  referenced_schema,
  referenced_table,
  string_agg(column_name,              ',' ORDER BY ordinality) AS columns,
  string_agg(referenced_column_name,   ',' ORDER BY ordinality) AS referenced_columns,
  CASE WHEN is_validated THEN 'VALID' ELSE 'NOT VALID' END AS validation_state
FROM cols
GROUP BY table_schema, table_name, fk_name, referenced_schema, referenced_table, is_validated
ORDER BY table_schema, table_name, fk_name;

C. Filter to one schema or table

-- One schema
... WHERE c.contype = 'f' AND n.nspname = '<schema_name>';

-- One table
... WHERE c.contype = 'f' AND t.relname = '<table_name>';

D. Search by partial FK name

SELECT conname,
       conrelid::regclass AS table_name,
       confrelid::regclass AS referenced_table
FROM pg_constraint
WHERE contype = 'f'
  AND conname ILIKE '%schedule%';

Why FKs can “go missing” in Postgres

• Schema name case. If a schema was created quoted with capital letters, you must match exact case.
• Partitions. An FK can live on the parent or on child partitions.
• NOT VALID. The FK exists but was not validated against existing rows.
• Permissions. information_schema can hide objects you don’t have rights to.
• Wrong database. Constraints are per database. Always double-check.

Validate or reapply a missing FK

-- Add as NOT VALID to avoid long locks on big tables
ALTER TABLE <schema>.<child_table>
ADD CONSTRAINT <fk_name>
FOREIGN KEY (<col_list>)
REFERENCES <schema>.<parent_table>(<col_list>)
NOT VALID;

-- Validate later online
ALTER TABLE <schema>.<child_table>
VALIDATE CONSTRAINT <fk_name>;

Part 2: SQL Server

Quick wins

A. List all foreign keys with columns, in order

SELECT
sch.name AS table_schema,
t.name AS table_name,
fk.name AS fk_name,
rsch.name AS referenced_schema,
rt.name AS referenced_table,
STUFF((
SELECT ‘,’ + COL_NAME(fkc.parent_object_id, fkc.parent_column_id)
FROM sys.foreign_key_columns fkc
WHERE fkc.constraint_object_id = fk.object_id
ORDER BY fkc.constraint_column_id
FOR XML PATH(”), TYPE).value(‘.’, ‘nvarchar(max)’), 1, 1, ”) AS columns,
STUFF((
SELECT ‘,’ + COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id)
FROM sys.foreign_key_columns fkc
WHERE fkc.constraint_object_id = fk.object_id
ORDER BY fkc.constraint_column_id
FOR XML PATH(”), TYPE).value(‘.’, ‘nvarchar(max)’), 1, 1, ”) AS referenced_columns,
fk.is_disabled,
fk.is_not_trusted
FROM sys.foreign_keys fk
JOIN sys.tables t ON t.object_id = fk.parent_object_id
JOIN sys.schemas sch ON sch.schema_id = t.schema_id
JOIN sys.tables rt ON rt.object_id = fk.referenced_object_id
JOIN sys.schemas rsch ON rsch.schema_id = rt.schema_id
ORDER BY table_schema, table_name, fk_name;

B. Filter to one schema or table

-- One schema
... WHERE sch.name = '<schema_name>';

-- One table
... WHERE t.name = '<table_name>';

C. Search by partial FK name

SELECT fk.name, OBJECT_SCHEMA_NAME(fk.parent_object_id) AS table_schema,
       OBJECT_NAME(fk.parent_object_id) AS table_name
FROM sys.foreign_keys fk
WHERE fk.name LIKE '%schedule%';

Why FKs can “go missing” in SQL Server

• Different database. Verify with SELECT DB_NAME();.
• Disabled or untrusted. is_disabled = 1 or is_not_trusted = 1 means the FK exists but isn’t enforced or trusted for query plans.
• Permissions. Limited rights can hide objects.
• Auto-naming. The FK may exist under a different name than expected.

Fix disabled or untrusted FKs

-- Re-enable and recheck
ALTER TABLE <schema>.<table> WITH CHECK CHECK CONSTRAINT <fk_name>;

-- If you must disable temporarily
ALTER TABLE <schema>.<table> NOCHECK CONSTRAINT <fk_name>;
-- Remember to bring it back WITH CHECK CHECK or it stays untrusted

Step by step: “I think some FKs are missing”

1. Confirm you’re in the right database.
2. List a few FKs without filters.
3. Search by fragments of the expected FK name.
4. Narrow down to the schema and table.
5. Check special cases (NOT VALID, disabled/untrusted).
6. Decide next action: validate, re-enable, or recreate.

---

Final Thoughts

Pulling a complete list of foreign keys is one of those things you’ll want in your toolkit for both Postgres and SQL Server. The catalog queries above give you the truth straight from the engine, and they also surface details like disabled or untrusted FKs in SQL Server and NOT VALID constraints in Postgres. Those are the little things that can cause confusion if you rely only on information_schema.

And one extra note if you’re ever migrating between the two: Postgres lets you reuse the same foreign key name on different tables, while SQL Server requires constraint names to be unique across the entire database. If you don’t catch that ahead of time, your migration scripts will fail when SQL Server sees duplicates. The easiest way around it is to stick to a convention like fk_<table>_<column> so you never run into naming collisions.

---

References

• PostgreSQL Docs: pg_constraint
• PostgreSQL Docs: information_schema.table_constraints
• Microsoft Docs: sys.foreign_keys (Transact-SQL)
• Microsoft Docs: sys.foreign_key_columns (Transact-SQL)
• Community: Brent Ozar – Foreign Keys and Performance

🔑 Foreign Key Cheat Sheet: Postgres & SQL Server

Postgres

List all FKs in the database

SELECT n.nspname AS table_schema,
       t.relname AS table_name,
       c.conname AS fk_name,
       rn.nspname AS ref_schema,
       rt.relname AS ref_table
FROM pg_constraint c
JOIN pg_class t  ON t.oid  = c.conrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_class rt ON rt.oid = c.confrelid
JOIN pg_namespace rn ON rn.oid = rt.relnamespace
WHERE c.contype = 'f'
ORDER BY table_schema, table_name, fk_name;

Include columns

SELECT conname, conrelid::regclass AS table_name,
       confrelid::regclass AS ref_table, convalidated
FROM pg_constraint
WHERE contype = 'f';

Search by name

SELECT conname, conrelid::regclass, confrelid::regclass
FROM pg_constraint
WHERE contype = 'f'
  AND conname ILIKE '%schedule%';

Check duplicates (migration prep)

SELECT conname, COUNT(*)
FROM pg_constraint
WHERE contype = 'f'
GROUP BY conname
HAVING COUNT(*) > 1;

SQL Server

List all FKs with columns

SELECT s.name AS schema_name,
       t.name AS table_name,
       fk.name AS fk_name,
       rs.name AS ref_schema,
       rt.name AS ref_table,
       fk.is_disabled, fk.is_not_trusted
FROM sys.foreign_keys fk
JOIN sys.tables t ON t.object_id = fk.parent_object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.tables rt ON rt.object_id = fk.referenced_object_id
JOIN sys.schemas rs ON rs.schema_id = rt.schema_id
ORDER BY schema_name, table_name, fk_name;

Search by name

SELECT fk.name, OBJECT_SCHEMA_NAME(fk.parent_object_id) AS schema_name,
       OBJECT_NAME(fk.parent_object_id) AS table_name
FROM sys.foreign_keys fk
WHERE fk.name LIKE '%schedule%';

Check disabled/untrusted

SELECT fk.name, fk.is_disabled, fk.is_not_trusted
FROM sys.foreign_keys fk;

Troubleshooting Steps

1. Confirm you’re in the right database.
2. Run the “list all” query — don’t filter yet.
3. Search by FK name fragments.
4. Narrow down by schema and table.
5. Watch for special cases:
   • Postgres → NOT VALID, partitions, duplicate names allowed.
   • SQL Server → is_disabled or is_not_trusted, unique constraint names required.

---

Migration Gotcha

• Postgres: allows the same FK name on different tables.
• SQL Server: FK names must be unique across the database.
  👉 Use a naming convention like fk_<table>_<column> to avoid collisions.