PostgreSQL Design Check Queries

Catalog of PostgreSQL queries for finding information about a PostgreSQL database and its design problems

	Choose collection:
AND	Choose query type:
AND	Choose query reliability:
AND	Choose category:	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.
AND	Choose data source:	From where does the query gets its information?
AND	Enter string:
AND	Has fixing queries?

All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
Statistics about the catalog content and project home in GitHub that has additional information.

There are 114 queries.

Seq nr	Name▲	Goal	Type	Data source	Last update	License	...
1	All foreign key constraints	Enforce referential integrity in database. Find all referential integrity (foreign key) constraints.	General	system catalog base tables only	2023-12-07 12:48	MIT License	View
2	All identifying relationships	Find all non-identifying relationships where the foreign key (a set of columns) is a subset of a uniqueness constraint (primary key, unique, or exclude constraint).	General	system catalog base tables only	2023-11-01 11:32	MIT License	View
3	All non-identifying relationships	Find all non-identifying relationships where the foreign key (a set of columns) is not a subset of any uniqueness constraint (primary key, unique, or exclude constraint).	General	system catalog base tables only	2023-11-01 11:31	MIT License	View
4	All short cycles (tables)	Find pairs of tables that have both a foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables.	General	INFORMATION_SCHEMA+system catalog base tables	2021-11-27 20:54	MIT License	View
5	A state machine is implemented with the help of a state classifier table	Find implementations of state machines that use a state classifier table.	General	system catalog base tables only	2023-11-26 15:39	MIT License	View
6	BOOLEAN base table and foreign table columns with a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves olnly this column	Find base table columns with the Boolean type that has a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves only this column. Avoid unnecessary constraints. It is quite improbable that there must be such constraints. For instance, a table with PRIMARY KEY () or UNIQUE () constraint can have at most two rows.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2022-11-03 10:46	MIT License	View
7	Candidate keys and foreign keys of tables that participate in an inheritance hierarchies	Find primary key, unique, foreign key, and exclude constraints that have been defined in tables that participate in an inheritance hierarchy. Do not forget to redefine the constraints that are defined on supertables also on their subtables.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
8	Chains of ON DELETE CASCADE	Find all referential paths (chains of of parent-child tables that are associated through foreign key constraints) where all foreign key constraints have ON DELETE CASCADE compensating actions. Be careful with too long chains.	General	system catalog base tables only	2022-10-29 20:04	MIT License	View
9	Column names that make joining more difficult (foreign key column name contains the table name)	Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different but the difference comes from the fact that the foreign key column name starts or ends with the table name. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax.	Problem detection	system catalog base tables only	2023-11-12 10:26	MIT License	View
10	Column names that make joining tables more difficult	Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax.	Problem detection	system catalog base tables only	2023-11-08 13:56	MIT License	View
11	Column names that make joining tables more difficult (quite similar names)	Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different and the difference is between two and four characters. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax.	Problem detection	system catalog base tables only	2023-11-08 13:56	MIT License	View
12	Column names that make joining tables more difficult (very similar names)	Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different and the difference is one character. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax.	Problem detection	system catalog base tables only	2023-11-08 13:56	MIT License	View
13	Completely overlapping foreign keys	Find completely overlapping foreign keys, i.e., the same set of columns of a table is covered by more than one foreign key constraint. These constraints could refer to the same table/key or different tables/keys.	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
14	Composite foreign keys	Find foreign keys that consist of more than one column. Make sure that the order of columns in the composite foreign key corresponds to the order of columns in the composite candidate key in the referenced table.	General	system catalog base tables only	2020-11-06 14:51	MIT License	View
15	Composite foreign keys with a mix of mandatory and optional columns	Find composite foreign keys with a mix of mandatory and optional columns. In case of a composite foreign keys all the columns should either optional or mandatory in order to avoid problems with NULLs.	Problem detection	system catalog base tables only	2021-02-25 17:29	MIT License	View
16	Composite foreign keys with an incorrect order of columns (ver 1)	Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key consist of columns with the same name but the order of columns in the keys is different. For instance, the query returns information about a foreign key (personal_code, country_code) that refers to the candidate key (country_code, personal_code). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause.	Problem detection	system catalog base tables only	2021-02-25 17:29	MIT License	View
17	Composite foreign keys with an incorrect order of columns (ver 2)	Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key are not the same in terms of data types of the columns. For instance, the query returns information about a foreign key that columns have the types (SMALLINT, INTEGER) that refers to the candidate key that columns have the types (INTEGER, SMALLINT). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause.	Problem detection	system catalog base tables only	2021-02-25 17:29	MIT License	View
18	Constraints that are not redefined in a subtable	Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) but not in its subtable. Unfortunately, PostgreSQL table inheritance is implemented in a manner that some constraints (CHECK, NOT NULL) are inherited from the supertable but others are not. "All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." (PostgreSQL documentation)	Problem detection	system catalog base tables only	2021-02-25 17:29	MIT License	View
19	Constraints that are not redefined in a subtable and there is no CHECK constraint that compensates this	Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) but not in its subtable. Additional condition is that in case of the subtable there is no CHECK that permits only one specific value in the constraint column. The presence of such check would make the design acceptable. Unfortunately, PostgreSQL table inheritance is implemented in a manner that some constraints (CHECK, NOT NULL) are inherited from the supertable but others are not. "All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." (PostgreSQL documentation)	Problem detection	INFORMATION_SCHEMA+system catalog base tables	2021-02-25 17:30	MIT License	View
20	Constraints that are not redefined in a subtable but there is a CHECK constraint that compensates this	Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) but not in its subtable. Exclude constraints where in case of the subtable there is a CHECK that permits only one specific value in the constraint column. The presence of such check would make the design acceptable. Unfortunately, PostgreSQL table inheritance is implemented in a manner that some constraints (CHECK, NOT NULL) are inherited from the supertable but others are not. "All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." (PostgreSQL documentation)	General	INFORMATION_SCHEMA+system catalog base tables	2020-12-25 16:20	MIT License	View