| 661 |
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 |
View |
| 662 |
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 |
View |
| 663 |
Simple natural primary keys |
Find primary keys that consist of one column and that values are not generated by the system. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2021-03-08 00:47 |
MIT License |
View |
| 664 |
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 |
View |
| 665 |
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 |
View |
| 666 |
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 |
View |
| 667 |
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 |
View |
| 668 |
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 |
View |
| 669 |
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 |
View |
| 670 |
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 |
View |
| 671 |
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 |
View |
| 672 |
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 |
View |
| 673 |
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 |
View |
| 674 |
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 |
View |
| 675 |
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 |
View |
| 676 |
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 |
View |
| 677 |
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 |
View |
| 678 |
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 |
View |
| 679 |
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 |
View |
| 680 |
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 |
View |