Seq nr | Name | Goal▲ | Type | Data source | Last update | License | ... |
---|---|---|---|---|---|---|---|
401 | Foreign key columns that are associated with a sequence generator | Find foreign key columns that are associated with a sequence generator. Foreign key values are selected amongst the values that are registered as corresponding primary key/unique key values. Values in the foreign key columns are not directly generated by the system. These values might be system generated indirectly - generated when a row is added to the primary (parent) table. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-08 00:42 | MIT License | |
402 | Foreign key column has a simple check constraint that is attached directly to the table | Find foreign key columns that are covered by a simple check constraint, i.e., the constraint involves only one column. Look only constraints that are directly associated with the table, i.e., are not specified through a domain. Perhaps the constraint should be defined on the referenced candidate key column. | Problem detection | system catalog base tables only | 2021-02-25 17:29 | MIT License | |
403 | Foreign key columns that have no index | Find foreign key columns that do not have an index. Foreign key columns are often used for performing join operations. It is useful to index such columns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |
404 | Foreign key columns that do not have an integer or varchar type | Find foreign key columns that do not have smallint, integer, bigint, or varchar(n) type. These are the most commonly used types in case of key/foreign key columns. Although the use of other types would be perfectly legal as well, make sure that you have selected the best possible data type for each and every column. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-11-16 10:14 | MIT License | |
405 | Optional foreign key columns | Find foreign key columns that do not have the NOT NULL constraint. It is better to limit the use of NULLs and optional columns due to the problems that it causes in interpreting the query results, making queries, and enforcing constraints. In addition, one should check as to whether this kind of design is consistent with the multiplicities in the conceptual data model. | General | INFORMATION_SCHEMA only | 2023-11-17 18:36 | MIT License | |
406 | Foreign key column has a default value that is not present in the parent table | Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |
407 | Gratuitous context in the names of foreign key columns | Find foreign key columns that name contains twice the name of the referenced (primary) table. | Problem detection | system catalog base tables only | 2021-02-25 17:29 | MIT License | |
408 | Too generic names (foreign key columns) | Find foreign key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a suffix that describes the nature of relationship with the referenced table or at least referes to the referenced table. These are too generic names. | Problem detection | system catalog base tables only | 2023-01-20 12:37 | MIT License | |
409 | Some candidate key values cannot be used as foreign key values | Find foreign key constraints in case of which some candidate key values cannot be used as foreign key values. Primary key/unique columns and foreign key columns should have the same data type and field size. If, for instance, the primary key column has type INTEGER and foreign key column has type SMALLINT, then one cannot use all the primary key values as foreign key values. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-10 12:13 | MIT License | |
410 | Inconsistent use of plural and singular in table names in the context of a relationship (English version) | Find foreign key constraints in case of which the name of one of the tables is in plural and the name of another table is in singular. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-01-13 13:41 | MIT License | |
411 | Inconsistent use of plural and singular in table names in the context of a relationship (Estonian version) | Find foreign key constraints in case of which the name of one of the tables is in plural and the name of another table is in singular. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-01-13 14:21 | MIT License | |
412 | 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 | |
413 | Inconsistent use of plural and singular in column names in the context of a relationship (English version) | Find foreign key constraints that cover one column in case of which the name of refererenced/referencing column is in plural and the name of referencing/refererenced column is in singular. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-01-13 14:14 | MIT License | |
414 | 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 | |
415 | 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 | |
416 | Foreign key references a non-key (has optional columns) | Find foreign key constraints that referenced column is optional. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-10 12:12 | MIT License | |
417 | 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 | |
418 | Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint | Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. | Problem detection | system catalog base tables only | 2021-02-25 17:30 | MIT License | |
419 | Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint while the referenced table has the primary key | Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns while at the same time the referenced table does have the primary key. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. | Problem detection | system catalog base tables only | 2021-02-25 17:30 | MIT License | |
420 | Foreign key references to a unique index columns not a unique key columns | Find foreign key constraints that reference to the columns that are covered by a unique index not a unique key. | Problem detection | system catalog base tables only | 2022-10-21 10:30 | MIT License |