| 961 |
Names of constraints (directly connected to a base table) and non-unique indexes that do not contain the associated column name |
Find constraints that are perhaps badly named. Find names of constraints (directly connected to a base table) and non-unique indexes that do not contain the associated column name. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 962 |
Names of constraints (directly connected to a base table) that do not contain the table name |
Find constraints that are perhaps badly named. Table names help us to ensure the uniqueness of the names within a schema and make the names more expressive and user-friendly. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 963 |
Names of indexes that do not contain the table name |
Find indexes that do not support a declarative constraint and that are perhaps badly named. Table names make the names more expressive and user-friendly. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 964 |
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 |
| 965 |
Not inherited CHECK constraints |
Find CHECK constraints 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. CHECK(false) on a supertable is an appropriate CHECK constraint if one wants to prevent registering data directly to the supertable, i.e., data can only be added to the subtables. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 966 |
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 |
| 967 |
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 |
| 968 |
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 |
| 969 |
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 |
| 970 |
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 |
| 971 |
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 |
| 972 |
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 |
| 973 |
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 |
| 974 |
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 |
| 975 |
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 |
| 976 |
One true lookup table |
This query identifies central classifier (reference) tables that store all or most of the system's reference values, along with the dependent tables that reference them. |
Problem detection |
system catalog base tables only |
2026-05-17 00:00 |
MIT License |
View |
| 977 |
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 |
| 978 |
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 |
| 979 |
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 |
| 980 |
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 |