| 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 |
Cascading update is not needed (based on surrogate keys) |
Find foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE SET DEFAULT in case of foreign keys that reference to surrogate keys. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2024-11-28 12:31 |
MIT License |
View |
| 9 |
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 |
| 10 |
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 |
| 11 |
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 |
| 12 |
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 |
| 13 |
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 |
| 14 |
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 |
| 15 |
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 |
| 16 |
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 |
| 17 |
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 |
| 18 |
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 |
| 19 |
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 |
| 20 |
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 |