| # | 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 | 2025-11-07 10:11 | 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 | 2025-11-07 10:11 | 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 | 2025-11-07 10:11 | 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 | 2025-11-07 10:11 | 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 | 2025-11-07 10:11 | 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 ( |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | 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 | 2025-11-07 10:11 | 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 | 2025-11-07 10:11 | MIT License | View |
| 9 | Chains of ON DELETE CASCADE | This query identifies referential paths (chains of foreign key relationships) where every constraint in the path is configured with ON DELETE CASCADE. The analysis is intended to uncover potential transitive deletion risks, where a single DELETE operation on a root table could trigger a catastrophic, cascading data loss across multiple, deeply-nested tables. The length of such chains is a key indicator of architectural fragility. | General | system catalog base tables only | 2025-11-08 10:50 | MIT License | View |
| 10 | Column names that make joining more difficult (foreign key column name contains the table name) | This query identifies foreign key columns where the identifier diverges from the referenced candidate key solely due to the redundant inclusion of the referencing table's name (as a prefix or suffix). Such naming redundancy precludes the use of the simplified SQL USING syntax in join operations, forcing the use of the more verbose ON clause. Harmonizing these column names (i.e., making the foreign key name identical to the referenced column name) enables more concise query formulation and improves schema readability. | Problem detection | system catalog base tables only | 2025-12-14 11:56 | MIT License | View |
| 11 | Column names that make joining more difficult (foreign key column name equals the referenced table name) | This query identifies foreign key columns where the identifier is identical to the name of the referenced table. This naming pattern typically results in a mismatch between the foreign key column and the referenced primary key column (e.g., a column named department referencing a table department with a primary key department_id). This mismatch precludes the use of the simplified ANSI SQL USING clause in join operations, necessitating the use of the more verbose ON clause. Harmonizing the column name to match the referenced key enables more concise query formulation. The Example: A table Employees has a column named Department that links to the Department table (where the ID is department_id). The Problem: Because the column is named Department and not department_id, you cannot use the shortcut syntax: JOIN Department USING (department_id). You are forced to write: JOIN Department ON Employees.Department = Department.department_id. |
Problem detection | system catalog base tables only | 2025-12-15 11:07 | MIT License | View |
| 12 | Column names that make joining tables more difficult | This query identifies foreign key columns where the identifier differs from the referenced candidate key identifier. It explicitly excludes self-referencing constraints (recursive relationships), where name divergence is structurally mandatory. The primary objective is to identify opportunities to harmonize column names across the schema. Synchronizing the foreign key name with the referenced column name facilitates the use of the ANSI SQL USING clause in join operations (e.g., JOIN t1 USING (client_id)), which is significantly more concise than the explicit ON predicate required when names differ. | Problem detection | system catalog base tables only | 2025-12-14 11:31 | MIT License | View |
| 13 | Column names that make joining tables more difficult (quite similar names) | This query identifies foreign key columns where the identifier deviates slightly from the referenced candidate key, specifically exhibiting a textual difference (Levenshtein distance) of two to four characters. This range typically captures minor prefixes (e.g., fk_) or suffixes that prevent the use of the concise SQL USING syntax in join operations. The query explicitly excludes self-referencing constraints (recursive relationships), where distinct column names are structurally mandatory. Aligning these names allows for cleaner, more readable query formulation. | Problem detection | system catalog base tables only | 2025-12-14 11:29 | MIT License | View |
| 14 | Column names that make joining tables more difficult (table names) | This query identifies foreign key relationships where the identifier of the referenced column diverges from the foreign key column solely due to the inclusion of the table name. It specifically targets cases where the referenced column name is formed by concatenating the target table name with the target column name (e.g., referenced_col = table_name || '_' || fk_col). This naming redundancy prevents the use of the simplified SQL USING syntax for join operations, necessitating verbose ON clauses. Harmonizing these identifiers by standardizing the naming convention improves schema readability and query conciseness. | Problem detection | system catalog base tables only | 2025-12-14 11:53 | MIT License | View |
| 15 | Column names that make joining tables more difficult (very similar names) | This query identifies foreign key columns where the identifier exhibits a minimal textual deviation (exactly one character) from the referenced candidate key. This specific proximity often indicates a typographical error or a singular/plural inconsistency (e.g., user_id vs users_id). The query explicitly excludes self-referencing constraints, where name divergence is structurally mandatory. Harmonizing these names enables the use of the simplified SQL USING syntax for joins, replacing verbose ON clauses and improving query readability. | Problem detection | system catalog base tables only | 2025-12-14 11:30 | MIT License | View |
| 16 | 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 | 2025-11-07 10:11 | MIT License | View |
| 17 | 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 | 2025-11-07 10:11 | MIT License | View |
| 18 | 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 | 2025-11-07 10:11 | MIT License | View |
| 19 | 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 | 2025-11-07 10:11 | MIT License | View |
| 20 | 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 | 2025-11-07 10:11 | MIT License | View |