| 581 |
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 |
| 582 |
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 |
| 583 |
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 |
| 584 |
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 |
| 585 |
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 |
| 586 |
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 |
| 587 |
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 |
| 588 |
Optional base table columns |
Find optional base table columns, i.e., columns that permit NULLs. Are you sure you want to allow NULLs in these columns? |
General |
INFORMATION_SCHEMA only |
2025-11-07 10:11 |
MIT License |
View |
| 589 |
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 |
| 590 |
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 |
| 591 |
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 |
| 592 |
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 |
| 593 |
Optional foreign key columns |
Find foreign key columns that do not have the NOT NULL constraint. It is better to limit the use of NULLs and optional columns due to the problems that it causes in interpreting the query results, making queries, and enforcing constraints. In addition, one should check as to whether this kind of design is consistent with the multiplicities in the conceptual data model. |
General |
INFORMATION_SCHEMA only |
2025-11-07 10:11 |
MIT License |
View |
| 594 |
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 |
| 595 |
Overlapping non-function based indexes that have the same leading column but with different operator class |
Find non-function based indexes (both unique and non-unique) that have identical first column but the operator class that is used in case of the first column is different. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 596 |
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 |
| 597 |
Overloading |
Make sure that there is genuine overloading instead of duplication or dead code. "In some programming languages, function overloading or method overloading is the ability to create multiple functions of the same name with different implementations." (Wikipedia) In PostgreSQL one can do it automagically by having multiple routines with the same name but different parameters in the same schema. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 598 |
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 |
| 599 |
Pairs of base tables that have at least two columns with the same names and data types |
What are the pairs of base tables that have at least two columns with the same names and data types. The tables might violate the principle of orthogonal design and hence might facilitate uncontrolled data redundancy over different tables. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-07 10:11 |
MIT License |
View |
| 600 |
Pairs of non-key column name and type pairs that have in different base tables a different default value |
Find non-key base table columns with the same name and type that have different default values. Be consistent. Columns with the same name and type shouldn't probably have different default values in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |