| Seq nr | Name▲ | Goal | Type | Data source | Last update | License | ... |
|---|---|---|---|---|---|---|---|
| 1 | All the non-primary key columns are optional | Find base tables where all he non-primary key columns are optional. Avoid too many optional columns. You have to be extra careful with NULLs in case of formulating search conditions of data manipulation statements. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:30 | MIT License | |
| 2 | All unique keys have at least one optional column | Find base tables where all unique keys (sets of columns covered by a unique constraint, or a unique index) have at least one optional column. In this case there can be rows in the table where the values that should identify the row are missing. Because NULL is not a value and is not duplicate of another NULL the, follwing is possible: CREATE TABLE Uniq(a INTEGER NOT NULL, b INTEGER, CONSTRAINT ak_uniq UNIQUE (a, b)); INSERT INTO Uniq(a, b) VALUES (1, NULL); INSERT INTO Uniq(a, b) VALUES (1, NULL); | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-21 11:54 | MIT License | |
| 3 | At most one row is permitted in a table (based on check constraints) | Find base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. | General | INFORMATION_SCHEMA+system catalog base tables | 2022-11-03 15:21 | MIT License | |
| 4 | Base table column of personal names has questionable properties | Find base table columns that name refers to the possibility that these are used to register personal names. Although there are very long personal names the general approach is to register a shortened version of these. Thus, a large field size is not a good idea because it would cause usability and security problems. There are persons who only have one name (mononymous persons). Database design must take it into account. | Problem detection | INFORMATION_SCHEMA only | 2022-10-29 20:35 | MIT License | |
| 5 | Base table columns permitting empty strings and strings that consist of only whitespace characters | Find non-foreign key columns of base tables that have a textual type and do not have any simple CHECK constraint, i.e., a constraint that involves only one column. Such columns can contain the empty string and strings that consist of only whitespace. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | |
| 6 | Base tables where all the columns are optional | Find base tables where all the columns are optional, i.e., permit NULLs. In such tables can be rows with no identity value and thus indistinguishable from other rows. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:30 | MIT License | |
| 7 | Base tables where all the unique columns are optional | Find the base tables where all the unique columns are optional. In such tables there can be rows without values that identify these rows. In this case there can be rows in the table where the values that should identify the row are missing. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-10-21 01:47 | MIT License | |
| 8 | CHECK constraints with IS NULL | Find CHECK constraints to one column (associated with a base table directly or through domain) that check that the value is missing (IS NULL). Write as simple constraint definitions as possible. By default columns are optional, i.e., they permit NULLs. NULL in a column means that checking of a CHECK constraint on the column results with UNKNOWN. CHECK constraints permit rows in case of which checking results with TRUE or UNKNOWN. In case of a CHECK constraint there is no need to check separately that a value in the column could be missing, i.e., be NULL. Thus, for instance, instead of writing CHECK (price>0 OR price IS NULL) write CHECK (price>0). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |
| 9 | Columns with only one value | Find base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | |
| 10 | Composite foreign keys with a mix of mandatory and optional columns | Find composite foreign keys with a mix of mandatory and optional columns. In case of a composite foreign keys all the columns should either optional or mandatory in order to avoid problems with NULLs. | Problem detection | system catalog base tables only | 2021-02-25 17:29 | MIT License | |
| 11 | Consistency of using NOT NULL constraints on Boolean base table columns | Find the number of mandatory and optional Boolean base table columns and the proportion of optional columns from all the Boolean columns. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the Boolean columns mandatory. | Sofware measure | INFORMATION_SCHEMA only | 2020-12-28 01:43 | MIT License | |
| 12 | Default is NULL | Find all tables where default value is NULL. NULL is the marker that denotes missing value. Implicitly all columns that do not have a default value have the default NULL. There is no need to specify such default value. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:30 | MIT License | |
| 13 | Duplicate NOT NULL constraints | Find columns that have NOT NULL constraint through a domain and also directly. Do not duplicate NOT NULL constraints in orde to avoid confusion and surprises. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |
| 14 | Empty columns | Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | |
| 15 | Empty tables | Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table. | Problem detection | system catalog base tables only | 2022-10-21 15:55 | MIT License | |
| 16 | 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 | |
| 17 | Incorrect check of NULLs | Find the use of =NULL and <>NULL in case of table level check constraints, domain level check constraints, WHEN clauses of triggers, WHERE clauses of rules, subqueries of derived tables, and bodies of routines. Write correct code. In order to determine as to whether a value is missing or not one has to use the IS [NOT] NULL predicate. NULL is the marker in SQL that denotes a missing value. Although it is often called "NULL value", one cannot treat it as an ordinary value, i.e., use it in comparisons as a value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-12 12:10 | MIT License | |
| 18 | IS NULL check is probably not needed | Find CHECK constraints that refer only to mandatory columns but the Boolean expression has IS NULL condition. | Problem detection | INFORMATION_SCHEMA only | 2022-06-09 13:57 | MIT License | |
| 19 | Mandatory columns for holding large textual values (comments, descriptions, etc.) | Find mandatory (NOT NULL) base table columns that name, column type, and field size refers to the possibility that these are used to register large textual values like comments, descriptions, and explanations. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License | |
| 20 | Mandatory non-primary key columns | Find mandatory non-primary key columns, i.e., the columns that have NOT NULL constraint. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-12-05 19:35 | MIT License |