| Seq nr | Name▲ | Goal | Type | Data source | Last update | License | ... |
|---|---|---|---|---|---|---|---|
| 1 | All sequence generators | Find all sequence generators. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-11-06 14:51 | MIT License | |
| 2 | Base tables that have a surrogate key and all its unique constraints have an optional column | A surrogate key is a primary key that consist of one column. The values of this column do not have any meaning for the user and the system usually generates the values (integers) automatically. In case of defining a surrogate key in a table it is a common mistake to forget declaring existing natural keys in the table. If a key covers an optional column then it does not prevent duplicate rows where some values are missing and other values are equal. 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 | Base tables that have a surrogate key and do not have any uniqueness constraints | A surrogate key is a key that consist of one column. The values of this column do not have any meaning for the user and the system generates the values (integers) automatically. In case of defining a surrogate key in a table it is a common mistake to forget declaring existing natural keys in the table. The query discards tables with only one column. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-26 17:47 | MIT License | |
| 4 | Base tables that have only the surrogate key and do not have any other column | Do not create unnecessary tables. If a table has cardinality 1 (one column), then most probably the values in this column should not be system generated unique values. | Problem detection | INFORMATION_SCHEMA only | 2021-03-08 00:41 | MIT License | |
| 5 | Definition of a non-minimal superkey instead of a candidate key (based on sequence generators) | Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define primary key's and unique constraints based on candidate keys. Find primary keys and unique constraints where a proper subset of the key is a column with an associated sequence generator. Most certainly such subset contains unique values. Thus, this subset should be the key, not its superset. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-07 20:56 | MIT License | |
| 6 | Domains that are associated with a sequence generator | Domains are reusable artifacts. By associating a domain with a sequence generator, one essentially starts to share sequence generators between tables. It may cause a potential performance bottleneck by having a shared resource. By having a shared sequence it is not possible to change properties of sequences of different tables independently, i.e., it increases coupling between tables. | Problem detection | INFORMATION_SCHEMA only | 2021-03-07 21:08 | MIT License | |
| 7 | Do not use approach that one size fits all (primary key columns) | Find base base tables have the simple primary key that contains the 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. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-03-18 20:58 | MIT License | |
| 8 | Do not use approach that one size fits all (unique index columns) | Find base base tables have a simple unique index (not associated with a constraint) that contains the column with the (case insensitive) name id and an integer type. In addition, the key values are generated automatically by the system by using a sequence generator. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-01-07 15:12 | MIT License | |
| 9 | 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 | |
| 10 | Grantable privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers | Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. | Problem detection | system catalog base tables only | 2024-01-07 13:43 | MIT License | |
| 11 | Mixing different mechanisms to generate surrogate values | Use the same mechanism of generating surrogate key values throughout the database. The use of SERIAL notation/explicitly creating a sequence generator and declaration of a column as an identity column will cause the creation of an external and internal sequence generator, respectively. Nevertheless, one should try to stick with using one of the mechanisms in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) | Problem detection | INFORMATION_SCHEMA only | 2021-03-08 00:42 | MIT License | |
| 12 | Multiple columns in the same base table that are associated with a sequence generator | Find base tables where multiple columns are associated with a sequence generator. Do not create unnecessary sequence generators. If one uses in a table a surrogate key, then it is enough to have one column where the values are generated by using a (external or internal) sequence generator. | Problem detection | INFORMATION_SCHEMA only | 2021-03-08 00:41 | MIT License | |
| 13 | 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 | |
| 14 | Perhaps a redundant column (based on sequence generators) | Find base tables where more than one column gets the default value by using the sequence generator mechanism. | Problem detection | INFORMATION_SCHEMA only | 2021-03-05 09:42 | MIT License | |
| 15 | Perhaps a unneccessary surrogate key | Find base tables that have the primary key that is not a surrogate key and an alternate key that is a surrogate key. Perhaps the surrogate key column is not needed. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-07 20:59 | MIT License | |
| 16 | Perhaps the type of a base table column/domain should be INTEGER/SMALLINT/BIGINT (based on sequence generators) | Specify for each column/domain a right data type that takes into account expected values in the column/domain. Find base table columns and domains that refer to the nextval function by using the default value mechanism but do not have the type INTEGER, SMALLINT, or BIGINT. This check is performed in case of identity columns: ERROR: identity column type must be smallint, integer, or bigint. | Problem detection | INFORMATION_SCHEMA only | 2021-03-04 11:24 | MIT License | |
| 17 | Potential duplication of sequence generators | Do not create unnecessary sequence generators. | Problem detection | INFORMATION_SCHEMA only | 2022-11-21 11:01 | MIT License | |
| 18 | Potentially missing PRIMARY KEY or UNIQUE constraints (based on sequence generators) | Find columns of base tables that contain automatically generated unique values but do not belong to any PRIMARY KEY/UNIQUE constraint. If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-07 20:53 | MIT License | |
| 19 | Potentially missing sequence generators (based on column names and types) | Find surrogate key columns that do not have an associated sequence generator. Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there are no sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-08 00:44 | MIT License | |
| 20 | Potentially unnecessary use of sequence generators | Find simple natural key columns that are associated with a sequence generator. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-07 21:06 | MIT License |