Cycles in relationships

Query goal:

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 about the query:

The query is usable starting from PostgreSQL (14). The query excludes foreign key constraints that refer to the same table, i.e., self references.

Query type:

Problem detection (Each row in the result could represent a flaw in the design)

Query reliability:

Low (Many false-positive results)

Query license:

MIT License

Data source:

INFORMATION_SCHEMA+system catalog

SQL query:

Click on query to copy it

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 where the query belongs to

Collection name	Collection 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 .

Collection name

Collection 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 where the query belongs to

Category name	Category 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.

Category name

Category 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.