Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list.
Notes
The query searches tables that implement an hierarchy based on the adjacency list pattern. The query is based only on the INFORMATION_SCHEMA views. A problem is that there could be multiple foreign key constraints with the same name in the same schema. It could make the result incorrect.
Type
General (Overview of some aspect of the database.)
SELECT DISTINCT rc.constraint_schema AS table_schema, fk_table.table_name, rc.constraint_name
FROM INFORMATION_SCHEMA.referential_constraints AS rc INNER JOIN INFORMATION_SCHEMA.key_column_usage AS fk_table USING (constraint_schema, constraint_name)
INNER JOIN INFORMATION_SCHEMA.constraint_table_usage AS pk_table ON (rc.unique_constraint_schema=pk_table.constraint_schema AND rc.unique_constraint_name=pk_table.constraint_name)
WHERE fk_table.table_schema=pk_table.table_schema
AND fk_table.table_name=pk_table.table_name
AND rc.constraint_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY rc.constraint_schema, fk_table.table_name;
Categories
This query is classified under the following categories:
Name
Description
Database design antipatterns
Queries of this category provide information about possible occurrences of SQL database design antipatterns.
Hierarchical data
Queries of this catergory provide information about storing hierarchical data in the database.
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.
Structure of base tables
Queries of this category provide information about the structuring of base tables at the database conceptual level
Further reading and related materials:
Reference
This is one of the antipatterns from the Bill Karwin's book of SQL antipatterns. See Chapter 3: Naive Trees.
Smell "Adjacency list": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018).