Skip to content

Instantly share code, notes, and snippets.

@zachcp

zachcp/List FK Relationships

Last active March 3, 2021 16:32
Show Gist options
  • Save zachcp/40050f8ad249fcd3c8c5109d362923ef to your computer and use it in GitHub Desktop.
Save zachcp/40050f8ad249fcd3c8c5109d362923ef to your computer and use it in GitHub Desktop.
Download ZIP
Foreign Key Ordering
Raw
foreign_key_ordering.sql
-- From https://www.cybertec-postgresql.com/en/postgresql-foreign-keys-and-insertion-order-in-sql/
WITH RECURSIVE fkeys AS (
/* source and target tables for all foreign keys */
SELECT conrelid AS source,
confrelid AS target
FROM pg_constraint
WHERE contype = 'f'
),
tables AS (
( /* all tables ... */
SELECT oid AS table_name,
1 AS level,
ARRAY[oid] AS trail,
FALSE AS circular
FROM pg_class
WHERE relkind = 'r'
AND NOT relnamespace::regnamespace::text LIKE ANY
(ARRAY['pg_catalog', 'information_schema', 'pg_temp_%'])
EXCEPT
/* ... except the ones that have a foreign key */
SELECT source,
1,
ARRAY[ source ],
FALSE
FROM fkeys
)
UNION ALL
/* all tables with a foreign key pointing a table in the working set */
SELECT fkeys.source,
tables.level + 1,
tables.trail || fkeys.source,
tables.trail @> ARRAY[fkeys.source]
FROM fkeys
JOIN tables ON tables.table_name = fkeys.target
/*
* Stop when a table appears in the trail the third time.
* This way, we get the table once with "circular = TRUE".
*/
WHERE cardinality(array_positions(tables.trail, fkeys.source)) < 2
),
ordered_tables AS (
/* get the highest level per table */
SELECT DISTINCT ON (table_name)
table_name,
level,
circular
FROM tables
ORDER BY table_name, level DESC
)
SELECT table_name::regclass,
level
FROM ordered_tables
WHERE NOT circular
ORDER BY level, table_name;
Raw
List FK Relationships
select kcu.table_schema || '.' ||kcu.table_name as foreign_table,
'>-' as rel,
rel_tco.table_schema || '.' || rel_tco.table_name as primary_table,
string_agg(kcu.column_name, ', ') as fk_columns,
kcu.constraint_name
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu
on tco.constraint_schema = kcu.constraint_schema
and tco.constraint_name = kcu.constraint_name
join information_schema.referential_constraints rco
on tco.constraint_schema = rco.constraint_schema
and tco.constraint_name = rco.constraint_name
join information_schema.table_constraints rel_tco
on rco.unique_constraint_schema = rel_tco.constraint_schema
and rco.unique_constraint_name = rel_tco.constraint_name
where tco.constraint_type = 'FOREIGN KEY'
group by kcu.table_schema,
kcu.table_name,
rel_tco.table_name,
rel_tco.table_schema,
kcu.constraint_name
order by kcu.table_schema,
kcu.table_name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment