| 601 |
No-operation routines with static return values |
This query identifies SQL routines that are functionally equivalent to a no-operation (no-op) instruction, meaning their sole operation is to return either a constant literal or an unmodified input parameter. Such routines provide no transformation or logic. They are typically superfluous and may represent placeholder code from early development, refactoring artifacts where original logic was deprecated, or simple logical oversights. Eliminating these functions reduces code clutter, simplifies application logic, and removes a marginal but unnecessary layer of computational overhead. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 11:06 |
MIT License |
View |
| 602 |
No point to have in a procedure COMMIT without ROLLBACK or vice versa |
If you end transaction in a procedure, then there should be a possibility to either commit or rollback the transaction based on some condition. Procedures appeared in PostgreSQL 11. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 603 |
Not enforced constraints |
This query identifies constraints (CHECK and FOREIGN KEY) that exist in the system catalog but are not actively enforced against the table data. |
Problem detection |
system catalog base tables only |
2025-11-15 10:10 |
MIT License |
View |
| 604 |
Not equals check in unstandardized way |
Find user-defined routines that use != operator to test as to whether two values are not equal. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 605 |
Not inherited CHECK constraints that are recreated in the immediate subtable |
Find base table CHECK constraints that have been defined as NOT INHERITED but the constraint with the same Boolean expression has been defined in the immediate subtable of the table. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 606 |
Not inherited CHECK constraints that cover at least one column |
Find CHECK constraints that cover at least one column and that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 607 |
NOT IN or <> ALL in derived tables |
Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor, especially in case of large data sizes. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 608 |
NOT IN or <> ALL in routines |
Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 609 |
NOT NULL constraint is directly associated with a column instead of the domain of the column |
Find mandatory (NOT NULL) base table columns that have been defined based on the same domain but the NOT NULL constraint is associated directly with the column not to the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 610 |
NOT NULL constraint via CHECK instead of NOT NULL constraint |
Find columns where the NOT NULL constraint has been added with the help of an explicit CHECK constraints instead of a NOT NULL constraint. One should note that internally NOT NULL constraints are treated as a kind of CHECK constraints. Nevertheless, the design should be consistent (i.e., NOT NULL is enforced with the same way in different places). |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-07 10:11 |
MIT License |
View |
| 611 |
NOT VALID foreign key constraints |
Find not valid foreign key constraints. These constraints have been created so that the existing data has not been checked against the constraint. It could be deliberate in case of legacy systems that have data quality problems. However, ideally all the data in the table conforms to the constraint. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 612 |
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 |
| 613 |
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 |
| 614 |
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 |
| 615 |
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 |
| 616 |
One true lookup table |
Find tables that contain all (or most) of the classifier values and tables that refer to these. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 617 |
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 |
| 618 |
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 |
| 619 |
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 |
| 620 |
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 |