| 621 | The generator of surrogate key values can output the same value more than once | Find surrogate keys where the generator can output the same value more than once. Key values must be unique, i.e., at some point the generator will prevent adding new rows to the table. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-08 00:40 | MIT License | |
| 622 | 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 | |
| 623 | The same sequence generator is used in case of multiple columns | Do not 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 (for instance the owner column or step), i.e., it increases coupling between tables. By having a shared sequence it is impossible to specify the owner (table column) to the sequence generator. | Problem detection | INFORMATION_SCHEMA only | 2021-03-07 21:07 | MIT License | |
| 624 | 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 | |
| 625 | Table columns that are associated with a sequence generator | Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there is no usage of 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. | General | INFORMATION_SCHEMA only | 2021-03-07 21:06 | MIT License | |
| 626 | 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 | |
| 627 | Surrogate key columns | Find surrogate keys. Surrogate key is a key that consist of one column, which has an integer type. The key has been declared by using PRIMARY KEY or UNIQUE constraint. The column is associated with a sequence generator (either external or internal, i.e., created by the system automatically because the column has been declared as an identity column). The column does not participate in any foreign key. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-03-07 20:59 | MIT License | |
| 628 | Base table columns with UUID type | Each column should have the most appropriate data type. If one does not want to have in a table the surrogate key that values are generated by using a sequence generator, then one may use instead a column with Universally Unique Identifiers as the key column. | General | INFORMATION_SCHEMA only | 2021-03-07 20:57 | MIT License | |
| 629 | 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 are not for the owner and not for the systemic user postgres. Make sure that there is the right amount of privileges for each and every relevant user. | General | system catalog base tables only | 2021-03-07 20:57 | MIT License | |
| 630 | 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 | |
| 631 | Potentially unused sequence generators | Find sequence generators that are not associated with any column through the default value mechanism. Please note, that it is also possible to refer to a sequence generator from a routine or from an application. If these are indeed not used, then these should be dropped, otherwise these are dead code. | Problem detection | INFORMATION_SCHEMA only | 2021-03-07 20:56 | MIT License | |
| 632 | The number of sequence generators in different schemas | Identify the number of sequence generators in different schemas. 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 at the database level. An alternative is to implement generation of unique values at the application level or at the database level by using triggers. However, such implementation would most probably lead to the performance penalty because adding new rows to the table must be serialized, i.e., it can be done in one session at a time. | Sofware measure | INFORMATION_SCHEMA only | 2021-03-07 20:55 | MIT License | |
| 633 | 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 | |
| 634 | Do not use a generic attribute table | Find base tables that implement a highly generic database design (EAV design - Entiry-Attribute-Value design), according to which attribute values are recorded in a generic table that contains attribute-value pairs. | Problem detection | INFORMATION_SCHEMA only | 2021-03-07 17:40 | MIT License | |
| 635 | Do not always depend on one's parent (INFORMATION_SCHEMA) | Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list. | General | INFORMATION_SCHEMA only | 2021-03-07 10:57 | MIT License | |
| 636 | The number of tables by schema, by type, and in total | Find the number of tables (base, foreign, and derived) in different schemas. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2021-03-07 10:57 | MIT License | |
| 637 | Do not use dual-purpose foreign keys | Find cases where the same column of a base table T is used to record references to multiple base tables. In addition, one has to add additional column to T for holding metadata about the parent table, referenced by the current row. | Problem detection | INFORMATION_SCHEMA only | 2021-03-07 10:56 | MIT License | |
| 638 | Small tables | Find tables that have one column or zero columns. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-03-07 10:52 | MIT License | |
| 639 | Pairs of non-key column name and type pairs that have in different base tables a different default value | Find non-key base table columns with the same name and type that have different default values. Be consistent. Columns with the same name and type shouldn't probably have different default values in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-05 21:01 | MIT License | |
| 640 | Pairs of non-key column name and type pairs that have in some base tables a default value and some cases not | Find non-key base table columns with the same name and type that have in some cases a default value and some cases not. Be consistent. Columns with the same name and type should probably either always have a default value in case of different tables or never have a default value in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-03-05 21:01 | MIT License | |