| 181 |
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 |
| 182 |
ON UPDATE CASCADE is perhaps missing (based on the compensating actions of other foreign key constraints) |
This query detects inconsistencies in the application of ON UPDATE CASCADE for foreign keys referencing the same candidate key. It identifies sets of foreign key constraints where a proper, non-empty subset is configured with ON UPDATE CASCADE. Such a mixed configuration is a functional flaw, as any attempt to update the parent key will be blocked by the non-cascading constraints, rendering the CASCADE action ineffective and preventing the intended data modification. The principle of atomicity requires that for any given key, either all referencing foreign keys have ON UPDATE CASCADE, or none do. |
Problem detection |
system catalog base tables only |
2025-11-08 10:44 |
MIT License |
View |
| 183 |
Foreign keys with ON UPDATE CASCADE |
This query generates a list of all foreign key constraints that are defined with the ON UPDATE CASCADE action. This list must be manually audited to enforce the design principle that this action should be applied exclusively to relationships involving mutable, natural keys. Any instance found referencing an immutable surrogate key should be considered a design flaw and remediated. |
General |
system catalog base tables only |
2025-11-08 10:40 |
MIT License |
View |
| 184 |
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 |
| 185 |
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 |
| 186 |
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 |
| 187 |
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 |
| 188 |
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 |
| 189 |
Base tables that have a surrogate key and do not have any uniqueness constraints |
This query identifies tables that use a single-column surrogate primary key but lack any other UNIQUE constraints or unique indexes. The absence of additional unique constraints suggests that the natural business key has not been enforced, creating a risk of data duplication that violates business rules. Tables consisting of only a single column are excluded from this check. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 15:29 |
MIT License |
View |
| 190 |
Perhaps incorrect check of permitted temporal values |
This query identifies potentially flawed CHECK constraints on columns of type timestamp or a timestamp range (e.g., tstzrange, daterange). It targets range checks where the upper bound of the value or the range is defined using an inclusive operator (<=). This is a common source of bugs, as a condition like column <= '2025-12-31' or UPPER(column) <= '2025-12-31' is interpreted as being up to 00:00:00 on that day, inadvertently excluding the entire last day of the intended period. The more robust pattern is to use an exclusive upper bound, such as column < '2026-01-01'. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 12:43 |
MIT License |
View |
| 191 |
Enumerated or range types with the same name in different schemas |
This query enforces the Don't Repeat Yorself principle across the database's type system. It identifies ENUM and RANGE types that share the same name but exist in different schemas. This indicates that a conceptual data type has been defined multiple times instead of having a single, canonical definition in a shared schema. Such duplication leads to maintenance overhead and the risk of semantic divergence over time. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 12:38 |
MIT License |
View |
| 192 |
Columns with a range type that require a better name |
This query identifies columns with a RANGE data type that violate naming conventions. It flags columns whose names do not semantically suggest a range or period, which can create ambiguity and lead to incorrect assumptions when writing queries. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-07 12:30 |
MIT License |
View |
| 193 |
Perhaps the type of a base table column should be TSTZRANGE |
This query identifies all base table columns defined with the range of timestamp without zone data type. Use of this data type is a potential design flaw as it can lead to ambiguity and bugs when handling data from multiple time zones. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-07 12:24 |
MIT License |
View |
| 194 |
Perhaps the type of a base table column should be TIMESTAMPTZ |
This query identifies all base table columns defined with the timestamp without time zone data type. Use of this data type is a potential design flaw as it can lead to ambiguity and bugs when handling data from multiple time zones. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-07 12:21 |
MIT License |
View |
| 195 |
Range lower bound can be NULL |
This query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL lower bound. This highlights ranges that can be "unbounded" on their starting side, which may be unintentional and could impact query logic and data constraints. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 12:03 |
MIT License |
View |
| 196 |
Range lower bound is not restricted |
This query finds range columns of base tables that are missing a safety check on their starting value. It looks for columns where the start of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their starting points. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 12:03 |
MIT License |
View |
| 197 |
Range lower bound restriction does not consider -infinity |
This query identifies RANGE type columns in base tables that have a NOT NULL constraint on their lower bound and an additional CHECK constraint, but this check does not account for -infinity. This may indicate a "magic number" problem, where a fixed lower limit (e.g., '1900-01-01') is used instead of the more explicit and semantically correct unbounded (-infinity) value. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 12:03 |
MIT License |
View |
| 198 |
Range upper bound can be NULL |
This query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL upper bound. This highlights ranges that can be "unbounded" on their ending side, which may be unintentional and could impact query logic and data constraints. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 12:03 |
MIT License |
View |
| 199 |
Range upper bound is not restricted |
This query finds range columns of base tables that are missing a safety check on their ending value. It looks for columns where the end of the range can be set to any value, without rules to ensure that value makes sense. This helps ensure that all ranges have proper limits defined for their ending points. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 12:03 |
MIT License |
View |
| 200 |
Range upper bound restriction dos not consider infinity |
This query identifies RANGE type columns in base tables that have a NOT NULL constraint on their upper bound and an additional CHECK constraint, but this check does not account for infinity. This may indicate a "magic number" problem, where a fixed upper limit (e.g., '2900-01-01') is used instead of the more explicit and semantically correct unbounded (infinity) value. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 12:03 |
MIT License |
View |