| 561 |
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 |
| 562 |
Number of columns covered with constraints |
For different types of constraints find the number of columns covered with constraints of such type. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 563 |
Number of derived tables that aggregate data |
Find the number of derived tables that aggregate data. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 564 |
Number of rows in base tables |
Find the number of rows in base tables. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 565 |
Number of system-generated and user-defined constraint names by constraint type (constraints that involve more than one column) |
Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. |
Sofware measure |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 566 |
Number of system-generated and user-defined constraint names by constraint type (constraints that involve one column) |
Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. |
Sofware measure |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 567 |
Number of tables covered by derived tables |
Find the number of base tables, the number of base tables that are referred from at least one derived table (view or materialized view), the number of base tables that are referred from at least one view, and the number of base tables that are referred from at least one materialized view. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 568 |
Number of underlying tables of derived tables |
Find for each view or materialized view the number of tables based on which the derived table has been directly defined. These tables could be base tables or derived tables. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 569 |
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 |
| 570 |
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 |
| 571 |
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 |
| 572 |
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 |
| 573 |
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 |
| 574 |
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 |
| 575 |
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 |
| 576 |
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 |
| 577 |
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 |
| 578 |
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 |
| 579 |
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 |
| 580 |
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 |