| 621 |
ON DELETE SET NULL is probably missing |
Find implementations of the adjacency list design pattern, where the corresponding foreign key columns are optional but the foreign key constraint does not have ON DELETE SET NULL compensating action. Implement adjacency list correctly. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 622 |
One true lookup table |
This query identifies central classifier (reference) tables that store all or most of the system's reference values, along with the dependent tables that reference them. |
Problem detection |
system catalog base tables only |
2026-05-17 00:00 |
MIT License |
View |
| 623 |
Only ID primary key |
Find base base tables have the simple primary key that contains a column with the (case insensitive) name id and an integer type. In addition, the primary key values are generated automatically by the system by using a sequence generator. In addition the base table must not have any unique constraint. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 624 |
Only one value permitted in a base table or a foreign table column (based on enumeration types) |
Find columns of base tables or foreign tables in case of which the type of the column permits only one value in the column. The type is an enumeration type that specifies only one value. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 625 |
Only one value permitted in a non-inherited base table or a foreign table column (based on check constraints) |
Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. Exclude columns that are inherited from a supertable because the constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 626 |
ON UPDATE CASCADE is missing (based on classifier tables) |
This query identifies foreign key constraints referencing classifier tables that lack an ON UPDATE CASCADE clause. Classifier tables hold reference data (e.g., status types, categories). While their rows should not be deleted if they are currently in use, any modifications to their underlying codes should automatically cascade to all dependent tables to maintain data integrity. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-17 00:08 |
MIT License |
View |
| 627 |
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 |
| 628 |
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 |
| 629 |
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 |
| 630 |
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 |
| 631 |
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 |
| 632 |
Optimistic locking routines lacking execution feedback |
This query identifies SQL routines that implement optimistic concurrency control via the xmin system column but fail to provide an execution status to the invoker. Specifically, it flags functions that perform UPDATE or DELETE operations filtered by xmin (a version check) but do not return information regarding the operation's success (e.g., a row count or a BOOLEAN status). This is a critical logic flaw; if a concurrency conflict occurs (the row was modified by another transaction), the operation yields zero rows. Without a return value, the failure is silent, leaving the calling application unaware that the data modification did not occur. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-15 11:35 |
MIT License |
View |
| 633 |
Optional base table columns that have a default value |
This query identifies columns that are both NULLable and have a DEFAULT value. This configuration represents a semantic contradiction: the DEFAULT clause implies that a value should always exist for the column, while the absence of a NOT NULL constraint explicitly permits the absence of a value. The presence of a DEFAULT strongly suggests the column's business logic requires a value, and therefore it should be defined with a NOT NULL constraint to enforce this consistently and make the schema's intent unambiguous. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-12 19:04 |
MIT License |
View |
| 634 |
Optional base table columns that participate in a UNIQUE constraint or index |
Find optional base table columns that participate in a UNIQUE constraint or index. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 635 |
Optional columns before mandatory columns |
Find cases where optional columns are before a mandatory column. Place mandatory columns before optional columns to improve comprehensibility of the table. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-07 10:11 |
MIT License |
View |
| 636 |
Optional composite foreign keys that do not have MATCH FULL specified |
Find optional composite foreign keys that do not have MATCH FULL specified. Without MATCH FULL the system will permit partial foreign key values |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 637 |
Optional non-foreign key base table columns that participate in a UNIQUE constraint or index |
Find optional base table columns that participate in a UNIQUE constraint or index but do not participate in a foreign key constraint. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 638 |
Overlapping non-function based indexes that have the same leading column with the same operator class |
Find non-function based indexes (both unique and non-unique) that duplicate each other because their first column is identical and the operator class that is used in case of the first column is identical. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 639 |
Overloading may cause runtime error |
Routines in the same schema that have the same name and that have parameters with different types or different number of parameters are not considered to be in conflict at the creation time. However, if defaults are provided in the definition of parameters, then these routines might be conflict during runtime. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 640 |
Oversized classifier code data typess |
This query identifies classifier tables where the code column uses a data type that accommodates unnecessarily large values for reference data. Specifically, it flags code columns defined as integer, bigint, or varchar(n) where n > 10. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-05-16 14:00 |
MIT License |
View |