| # | Name ▲ | Goal | Type | Data source | Last update | License | |
|---|---|---|---|---|---|---|---|
| 1 | All foreign key constraints | Enforce referential integrity in database. Find all referential integrity (foreign key) constraints. | General | system catalog base tables only | 2025-11-07 10:11 | MIT License | View |
| 2 | Cascading update is not needed (based on surrogate keys) | Find foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE SET DEFAULT in case of foreign keys that reference to surrogate keys. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 3 | Chains of ON DELETE CASCADE | This query identifies referential paths (chains of foreign key relationships) where every constraint in the path is configured with ON DELETE CASCADE. The analysis is intended to uncover potential transitive deletion risks, where a single DELETE operation on a root table could trigger a catastrophic, cascading data loss across multiple, deeply-nested tables. The length of such chains is a key indicator of architectural fragility. | General | system catalog base tables only | 2025-11-08 10:50 | MIT License | View |
| 4 | Foreign keys with ON DELETE CASCADE | This query identifies all foreign key constraints that use ON DELETE CASCADE for the purpose of a design audit. The results must be manually reviewed to verify that each use case correctly implements a specific conceptual relationship. According to design principles, ON DELETE CASCADE is only appropriate for implementing generalization hierarchies (is-a relationships), compositions (strong ownership), or the existential dependency of a non-main entity on a main entity. Any usage outside of these patterns is considered a potential design flaw. | General | system catalog base tables only | 2025-11-08 10:51 | MIT License | View |
| 5 | Foreign keys with ON UPDATE CASCADE | This query generates a list of all foreign key constraints that are defined with the ON UPDATE CASCADE action. This list must be manually audited to enforce the design principle that this action should be applied exclusively to relationships involving mutable, natural keys. Any instance found referencing an immutable surrogate key should be considered a design flaw and remediated. | General | system catalog base tables only | 2025-11-08 10:40 | MIT License | View |
| 6 | Inconsistent chain of relationships in terms of using ON UPDATE compensating action | In case of a chain of relationships between tables (where the primary key and the foreign key have the same columns) the use of ON UPDATE compensating action should be consistent. For instance, in the next example there is inconsistency, because if one changes the person_code in table Person, then the modification does not succeed because it does not cascade to the table Product. It is unclear as to whether it should be possible to change the person_code or not. Person (person_code, surname) Primary key (person_code) Worker(person_code) Primary key (person_code) Foreign key (person_code) References Person (person_code) ON UPDATE CASCADE Product(product_code, registrator) Primary key (product_code) Foreign key (registrator) References Worker (person_code) ON UPDATE NO ACTION |
Problem detection | system catalog base tables only | 2025-11-07 10:11 | MIT License | View |
| 7 | 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 |
| 8 | 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 |
| 9 | 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 |
| 10 | 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 |
| 11 | 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 |
| 12 | 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 |
| 13 | 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 |
| 14 | 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 |
| 15 | 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 |
| 16 | SET DEFAULT compensatory action is unsuitable | Find foreign keys with SET DEFAULT compensatory action where the foreign key column does not have a default value. Compensatory actions cannot make changes that violate integrity constraints in a database. SET DEFAULT means that there shoud be a default value at the foreign key column. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 17 | SET NULL compensatory action is unsuitable | Find foreign key constraints that use a SET NULL compensating action but a foreign key column is mandatory, i.e., does not permit NULLs. Compensatory actions cannot make changes that violate integrity constraints in a database. SET NULL cannot put NULL to a mandatory column (delete a foreign key value). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-07 10:11 | MIT License | View |
| 18 | The number of compensating actions of foreign key constraints | Find the number of compensating actions that are specified in case of foreign key constraints. | Sofware measure | system catalog base tables only | 2025-11-07 10:11 | MIT License | View |