| 541 | 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 | 2021-11-04 13:20 | MIT License | |
| 542 | ON DELETE CASCADE is not needed (based on classifier tables) | Find foreign key constraints with ON DELETE CASCADE compensating action that refer to classifier (reference data) tables. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-12-08 14:52 | MIT License | |
| 543 | ON DELETE CASCADE is probably missing (based on the multiplicity of the relationship) | Find foreign key constraints that completely overlap with a candidate key constraint (primary key or unique constraint) but the foreign key constraint does not have the ON DELETE CASCADE compensating action. In this case there is a foreign key that implements a relationship type between a strong entity type and a weak entity type (1-1 relationship type). Therefore, in this case ON DELETE CASCADE is an appropriate compensating action. | Problem detection | system catalog base tables only | 2021-02-25 17:29 | MIT License | |
| 544 | ON DELETE CASCADE is probably not needed (based on the relationship type) | Find foreign key constraints that implement a non-identifying relationship type and have ON DELETE CASCADE compensating action. If the identity of the parent table is not a part of the identity of the child table, then there is a non-identifying relationship type and most probably the foreign key should not have ON DELETE CASCADE. | Problem detection | system catalog base tables only | 2023-10-28 18:38 | MIT License | |
| 545 | 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 | 2021-02-25 17:29 | MIT License | |
| 546 | 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 | 2023-01-06 13:39 | MIT License | |
| 547 | 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 | 2021-02-25 17:29 | MIT License | |
| 548 | 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 | 2022-11-09 15:15 | MIT License | |
| 549 | Only one value permitted in a base table or a foreign table column (based on check constraints) | Find columns of base tablesor 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 | 2020-12-25 17:57 | MIT License | |
| 550 | 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 | 2021-02-25 17:30 | MIT License | |
| 551 | 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 | 2021-02-25 17:29 | MIT License | |
| 552 | ON UPDATE CASCADE 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 in case of foreign keys that reference to surrogate keys. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-04-30 18:39 | MIT License | |
| 553 | ON UPDATE CASCADE is perhaps missing (based on the compensating actions of other foreign key constraints) | Find inconsistencies of using ON UPDATE CASCADE in case of foreign key constraints. An example of inconsistency is that there are two foreign key constraints in different tables that refer to the same table and its candidate key. One of the constraints has ON UPDATE CASCADE compensating action one does not. ON UPDATE CASCADE usage should be consistent, otherwise it does not allow us to change key values in the primary table. More generally, there is a set of foreign key constraints F that refer to a candidate key of table T. It cannot be the case that a proper non-empty subset of these foreign keys have ON UPDATE CASCADE compensatory action. Either all the constraints in F should have it or none of it should have it (depending on circumstances). | Problem detection | system catalog base tables only | 2022-04-30 18:39 | MIT License | |
| 554 | ON UPDATE CASCADE is probably missing (based on column names) | Find foreign key constraints that do not feature ON UPDATE CASCADE compensating action although people have a reason to change the key value in the primary table by assuming that the names of foreign key columns correctly point towards the use of natural keys in the table. | Problem detection | system catalog base tables only | 2023-11-16 11:59 | MIT License | |
| 555 | ON UPDATE CASCADE is probably missing (based on data types) | Find foreign key constraints where the foreign key column does not have an integer type or uuid type and the foreign key constraint does not have ON UPDATE CASCADE compensating action. In this case the foreign key probably refferes to a natural key (i.e., a key that values have meaning outside the computer system) and ON UPDATE CASCADE would be suitable because the key values could be changed over time. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-06 14:19 | MIT License | |
| 556 | ON UPDATE CASCADE is probably missing (based on the properties of the referenced column) | Find the foreign key constraints that do not have ON UPDATE CASCADE and that referenced key is a simple key that has an integer type, is not covered by another foreign key, and does not have an associated sequence generator, i.e., the foreign key references a simple natural key. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-04-30 18:39 | MIT License | |
| 557 | 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 | 2020-11-21 03:02 | MIT License | |
| 558 | Optional base table columns that have a default value that is not the empty string | Find optiona base table columns that have a default value that is not the empty string. Such columns should be mandatory. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:29 | MIT License | |
| 559 | Optional base table columns that participate in a UNIQUE constraint or index | Find optional base table columns that participate in a UNIQUE constraint or index. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-10-21 15:57 | MIT License | |
| 560 | Optional columns before mandatory columns | Find cases where optional columns are before a mandatory column. Place mandatory columns before optional columns to improve comprehensibility of the table. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:29 | MIT License | |