| # | Name | Goal ▲ | Type | Data source | Last update | License | |
|---|---|---|---|---|---|---|---|
| 901 | 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 |
| 902 | 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 |
| 903 | 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 |
| 904 | ON UPDATE CASCADE is probably missing (based on column names) | This query identifies foreign key constraints that are likely missing an ON UPDATE CASCADE action. It operates on the heuristic that foreign keys referencing a natural key should permit cascading updates. The query uses a naming convention to identify probable natural keys, specifically flagging foreign key columns having the suffix or prefix "code" on the assumption that such values are user-defined and may require modification. The absence of ON UPDATE CASCADE on these keys can lead to referential integrity violations when the parent key is updated. | Problem detection | system catalog base tables only | 2025-11-08 10:22 | MIT License | View |
| 905 | ON UPDATE CASCADE is probably missing (based on the properties of the referenced column) | This query identifies foreign key constraints that are likely missing an ON UPDATE CASCADE action on what is inferred to be a simple, integer-based natural key. It isolates referenced keys that are single integer columns, are not themselves foreign keys, and, crucially, have no associated sequence generator. This combination strongly indicates a manually managed key whose values might change. The absence of ON UPDATE CASCADE on such relationships can lead to significant data maintenance issues if the natural key value ever needs to be updated. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-08 10:30 | MIT License | View |
| 906 | ON DELETE CASCADE is probably missing (based on the multiplicity of the relationship) | This query identifies foreign key constraints that likely require an ON DELETE CASCADE action. It targets a specific pattern: where the foreign key constraint's columns are identical to a candidate key (Primary Key or Unique constraint) of the same table. This structure implements a one-to-one identifying relationship, where the child entity is existentially dependent on the parent (a weak entity depending on a strong entity). The absence of ON DELETE CASCADE in this scenario is a design flaw, as it prevents the parent row from being deleted and breaks the conceptual model. | Problem detection | system catalog base tables only | 2025-11-08 10:53 | MIT License | View |
| 907 | ON UPDATE CASCADE is probably missing (based on data types) | This query identifies foreign key constraints that likely require an ON UPDATE CASCADE action. It operates on a strong heuristic based on data type: the query flags foreign keys where the column type is neither integer nor uuid and the ON UPDATE CASCADE action is absent. This assumes that non-numeric, non-UUID keys are natural keys (e.g., VARCHAR, CHAR) whose values are meaningful and may need to be updated over time. The absence of ON UPDATE CASCADE on these relationships can impede data maintenance and violate referential integrity upon updates. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-08 10:33 | MIT License | View |
| 908 | ON DELETE CASCADE is probably not needed (based on the relationship type) | This query identifies foreign key constraints that use ON DELETE CASCADE in a non-identifying relationship. A relationship is considered non-identifying if the foreign key columns in the child table are not part of the child table's candidate key. In such cases, the child entity has its own independent identity, and using ON DELETE CASCADE is often a design flaw that can lead to unexpected and catastrophic data loss. | Problem detection | system catalog base tables only | 2025-11-07 20:02 | MIT License | View |
| 909 | ON UPDATE CASCADE is probably not needed (based on column names) | This query identifies foreign key constraints with a superfluous ON UPDATE CASCADE action. It operates on the heuristic that foreign keys referencing surrogate keys should not permit cascading updates, as their values are immutable by definition. The query uses a naming convention to identify probable surrogate keys, specifically flagging foreign key columns with the prefix or suffix "id". The presence of ON UPDATE CASCADE on such keys is not only unnecessary but also misrepresents the nature of the relationship to a schema observer. | Problem detection | system catalog base tables only | 2025-11-08 10:29 | MIT License | View |
| 910 | ON DELETE CASCADE is not needed (based on classifier tables) | This query identifies foreign key constraints with ON DELETE CASCADE that reference classifier tables. Classifier tables hold reference data (e.g., status types, categories), and their rows should not be deleted if they are in use. Applying ON DELETE CASCADE to such a relationship is a critical design flaw, as it creates a direct path for the deletion of a single lookup value to trigger the mass deletion of operational business data. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-08 10:49 | MIT License | View |
| 911 | 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 |
| 912 | Updatable foreign tables that refer to another PostgreSQL table | This query identifies foreign tables established via the postgres_fdw (PostgreSQL Foreign Data Wrapper) that are configured to permit data modification (updatability). While postgres_fdw supports INSERT, UPDATE, and DELETE operations on remote tables, enabling this capability introduces complexity regarding distributed transactions, performance, and security. The query serves as an audit tool to verify that the updatability of these foreign tables is a deliberate architectural requirement and not an unintended default configuration. | General | INFORMATION_SCHEMA only | 2025-11-20 11:45 | MIT License | View |
| 913 | Identity columns configured as GENERATED BY DEFAULT | This query identifies identity columns defined with the GENERATED BY DEFAULT clause. Unlike GENERATED ALWAYS, this configuration permits manual insertion of values into the identity column without explicit overrides. This flexibility creates a significant risk of sequence desynchronization: if a user manually inserts a key value that exceeds the current sequence state, the sequence will eventually generate a colliding value. This results in runtime unique constraint violations (primary key conflicts) that are difficult to predict and resolve. The preferred pattern for surrogate keys is typically GENERATED ALWAYS to enforce system-controlled uniqueness. | Problem detection | INFORMATION_SCHEMA only | 2025-12-26 14:45 | MIT License | View |
| 914 | Inconsistent CHECK constraints on columns with the same name | This query identifies inconsistencies in data validation for columns that share the same name across different tables. It flags cases where a conceptual data element (e.g., 'email', 'postal_code') is subject to a CHECK constraint in some tables but lacks one in others. This violates the principle of uniform data integrity, creates semantic ambiguity, and can allow invalid data to enter the system through the unconstrained columns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 09:53 | MIT License | View |
| 915 | Inconsistent use of length and char_length functions | This query identifies inconsistent usage of string length functions within the database. Although length() and char_length() are functional synonyms in PostgreSQL (both returning the character count), mixing them violates clean coding principles. The query checks if both variants are present in the codebase, flagging a lack of standardization. Enforcing a single choice (typically the SQL-standard char_length or character_length) improves code maintainability and readability. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 12:56 | MIT License | View |
| 916 | Incorrect prevention of the empty string or strings that consist of only spaces in a field | This query identifies ineffectual CHECK constraints on base and foreign table columns that incorrectly attempt to prohibit empty or whitespace-only strings using the predicate trim(column_name) IS NOT NULL. Due to PostgreSQL's strict distinction between an empty string ('') and NULL, this check is a tautology; trim('') evaluates to '', and the condition '' IS NOT NULL is always true. The query finds these logically flawed constraints, which fail to provide any data validation and permit the insertion of the exact values they were intended to prevent. | Problem detection | INFORMATION_SCHEMA only | 2025-11-12 14:56 | MIT License | View |
| 917 | Redundant explicit locking in initially deferred constraint triggers | This query identifies INITIALLY DEFERRED constraint triggers that utilize explicit locking mechanisms (e.g., LOCK TABLE, SELECT ... FOR SHARE). Deferred constraints are evaluated at transaction commit time, by which point the database engine automatically manages the necessary data consistency states. Consequently, acquiring manual locks within these triggers is technically redundant. Furthermore, it introduces performance risks by enforcing serialization at the critical end-stage of the transaction, potentially increasing the likelihood of deadlocks and reducing system throughput. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-10 13:29 | MIT License | View |
| 918 | Many-to-many tables that need conceptual renaming | This query identifies junction tables (implementing a binary relationship via two foreign keys) whose names are simple concatenations of the parent table names (e.g., Course_Lecturer). This naming style is flagged as a design smell because it merely describes the physical implementation rather than the conceptual relationship being modeled. The recommended best practice is to rename such tables to reflect the domain concept they represent. For instance, the relationship between Course and Lecturer should be named after the activity it represents, such as Teaching or Course_Assignment. | Problem detection | system catalog base tables only | 2025-11-15 09:45 | MIT License | View |
| 919 | Many-to-many tables that perhaps need conceptual renaming | This query identifies junction tables (implementing a binary relationship via two foreign keys) whose names are simple concatenations of the parent table names, often with minor variations (e.g., Courses_Lecturer). This naming convention is a design smell as it describes the physical implementation rather than the conceptual relationship being modeled. The recommended best practice is to rename such tables to reflect the domain concept they represent. For instance, the relationship between Course and Lecturer should be named after the activity it represents, such as Teaching or Course_Assignment. | Problem detection | system catalog base tables only | 2025-11-15 09:46 | MIT License | View |
| 920 | Too generic names (many-to-many relationship types that do not have additional attributes) | This query identifies junction tables with semantically redundant names, specifically targeting those whose names contain the word "join". The structure of a many-to-many table (typically two foreign keys forming a composite primary key) is inherently declarative of its role as a join mechanism. Including the word "join" in the name is superfluous and violates the principle of naming based on the entity or relationship being modeled, not the implementation detail. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-10 09:41 | MIT License | View |