| 961 |
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 |
| 962 |
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 |
| 963 |
One-to-one relationships |
Find one-to-one relationships between base tables. In this case the foreign key columns must have primary key or unique constraint. These tables could implement inheritance hierarchy that has been specified in the conceptual data model. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 964 |
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 |
| 965 |
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 |
| 966 |
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 |
| 967 |
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 |
| 968 |
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 |
| 969 |
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 |
| 970 |
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 |
| 971 |
Patterns of the names of columns of simple primary keys |
Find the patterns of the names of columns of simple primary keys. Make sure that the naming is consistent. Ideally, the names should indicate as to whether the column is a surrogate or a natural key column. PostgreSQL 14 added primary keys, unique constraints, and foreign keys to system catalogs. Thus the query was modified to exclude the results from the system catalog. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 972 |
Percentage of the total index storage size from the total database storage size (system catalog included) |
Get overview of disk usage. |
Sofware measure |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 973 |
Perhaps an inconsistent use of NO ACTION and RESTRICT in the foreign key declarations |
Find as to whether in case of foreign key constraints both the compensating actions RESTRICT and NO ACTION are used within the same database. If the same thing has to do in different places, then try to do it in the same way. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 974 |
Perhaps a relationship should be irreflexive |
Enforce all the constraints. A binary relation is called irreflexive, if it does not relate any element to itself. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 975 |
Perhaps a too generic foreign key column name |
Find the names of foreign key columns that are too generic. The expressive names of table columns allow database users better and more quickly understand the meaning of data in the database. A person could participate in a process or be associated with an object due to different reasons. Thus, foreign key column names like isik_id, person_id, tootaja_id, worker_id etc. are too generic. The name should refer (also) to the reason why the person is connected. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 976 |
Perhaps primary key columns could be renamed |
Find the names of simple primary key columns that name does not follow the pattern _id or _code but it is quite similar. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 977 |
Perhaps unnecessary privileges to use the database |
Find as to whether a database user (except postgres), who is not a superuser, has Create (C) or Temporary (T) privileges to use the database. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 978 |
Perhaps un-trimmed string concatenation in derived tables |
This query identifies derived tables (views and materialized views) containing string concatenation logic that introduces potential leading or trailing whitespace. It targets expressions using the concatenation operator (||), concat(), or format() that may inject separators (such as spaces) but lack a surrounding trim() function. This pattern often results in "dangling separators" when one of the concatenated components is null or empty, degrading data quality and presentation. |
Problem detection |
system catalog base tables only |
2025-12-22 18:28 |
MIT License |
View |
| 979 |
Personal names are unique |
Find all primary key and unique constraints of base tables that involve at least one column with personal names. Personal names are not unique and these cannot be (usually) used to guarantee uniqueness. Either one has declared a superkey instead of candidate key (i.e., the key contains a column that is not needed for uniqueness) or the key columns have been found incorrectly. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 980 |
Privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers |
This query performs a comprehensive security audit of access control lists (ACLs) across a wide range of database objects, including the database itself, schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers. It retrieves privileges granted to any principal other than the object's owner or the systemic postgres superuser. The objective is to identify and validate "third-party" access rights, ensuring compliance with the principle of least privilege and preventing unauthorized access accumulation. |
General |
system catalog base tables only |
2026-01-21 10:52 |
MIT License |
View |