| 561 |
Number of used tables |
Find statistics about how many derived tables have how many different underlying tables. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 562 |
Number of using views |
Find statistics about how many base tables have how many derived tables that use these tables. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 563 |
Number of views with and without security barrier |
Find the number of views, the number of views with and without security barrier setting, and the names of views with and without the security barrier setting. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 564 |
Numeric literals between apostrophes |
Placing numeric literals between apostrophes will cause unnecessary type conversions. It could also be that the literal should indeed be textual but the problem is in choosing the values. For instance, table Occupation has column occupation_code with the type VARCHAR(3). However, all the values in the column consist of digits (for instance, 1, 2, 3). Thus, it would have been better to a) use SMALLINT as the column type or b) use different occupation codes that contain additional symbols to digits. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 565 |
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 |
| 566 |
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 |
| 567 |
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 |
| 568 |
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 |
| 569 |
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 |
| 570 |
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 |
| 571 |
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 |
| 572 |
Only one value permitted in a 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. The constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable or is used to enfore the rule that the table can have at most one row. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 573 |
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 |
| 574 |
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 |
| 575 |
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 |
| 576 |
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 |
| 577 |
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 |
| 578 |
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 |
| 579 |
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 |
| 580 |
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 |