Find as to whether foreign key constraints between tables form a cycle. For instance, if table C refers to table B, table B refers to table A, table A refers to table C and all the foreign key columns are mandatory, then one cannot add data to the tables. Is cycle value in the result should not contain value TRUE. One should check manually as to whether the foreign key columns are mandatory or optional.
Notes
The query is usable starting from PostgreSQL (14). The query excludes foreign key constraints that refer to the same table, i.e., self references.
Type
Problem detection (Each row in the result could represent a flaw in the design)
WITH RECURSIVE fk AS (SELECT
o.conname,
(SELECT nspname FROM pg_namespace WHERE oid=f.relnamespace) AS foreign_schema,
f.relname AS foreign_table,
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS target_schema,
c.relname AS target_table
FROM pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class f on f.oid = o.confrelid
WHERE o.contype = 'f'),
fk_input AS (SELECT target_schema, target_table, foreign_schema, foreign_table
FROM fk
WHERE foreign_schema || '.' || foreign_table <> target_schema || '.' || target_table
UNION SELECT table_schema, table_name, NULL AS foreign_schema, NULL AS foreign_table
FROM INFORMATION_SCHEMA.tables AS t
WHERE t.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
AND table_type='BASE TABLE'
AND NOT EXISTS (SELECT *
FROM fk
WHERE t.table_schema=fk.target_schema
AND t.table_name=fk.target_table)),
fk_hierarchy AS (SELECT target_schema, target_table, foreign_schema, foreign_table, 1 AS level
FROM fk_input
WHERE foreign_schema IS NULL
AND foreign_table IS NULL
UNION ALL
SELECT f.target_schema, f.target_table, f.foreign_schema, f.foreign_table, fh.level+1 AS level
FROM fk_input AS f INNER JOIN fk_hierarchy AS fh ON f.foreign_schema=fh.target_schema AND f.foreign_table=fh.target_table)
CYCLE target_schema, target_table SET is_cycle USING path
SELECT target_schema, target_table, foreign_schema, foreign_table, level, is_cycle, path
FROM fk_hierarchy
WHERE EXISTS (SELECT *
FROM fk_hierarchy
WHERE is_cycle='t')
ORDER BY is_cycle DESC, level, target_schema, target_table;
Collections
This query belongs to the following collections:
Name
Description
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories
This query is classified under the following categories:
Name
Description
Does not work in some earlier PostgreSQL version
Queries of this category provide information that was not available in some earlier PostgreSQL version
Relationships between tables
Queries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly.