| 181 |
Comments of derived tables |
Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information. |
General |
system catalog base tables only |
2023-01-19 12:14 |
MIT License |
View |
| 182 |
Comments of non-derived tables |
Find comments of non-derived tables (base tables, foreign tables, and partitioned tables) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, constraints, indexes, triggers, rules). Make sure that the comments give relevant, useful, and correct information. |
General |
system catalog base tables only |
2023-01-19 12:30 |
MIT License |
View |
| 183 |
Comments of routines |
Find comments of user-defined routines (functions or procedures) that are registered in the system catalog witht a COMMENT statement. Make sure that the comments give relevant, useful, and correct information. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2020-11-06 14:51 |
MIT License |
View |
| 184 |
Comments of schemas, sequences, types, domains, domain constraints, and event triggers |
Find all the comments that have been added with a COMMENT statement to schemas, sequences, types, domains, and event triggers. |
General |
system catalog base tables only |
2023-01-19 15:04 |
MIT License |
View |
| 185 |
Completely overlapping foreign keys |
Find completely overlapping foreign keys, i.e., the same set of columns of a table is covered by more than one foreign key constraint. These constraints could refer to the same table/key or different tables/keys. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |
| 186 |
Composite foreign keys |
Find foreign keys that consist of more than one column. Make sure that the order of columns in the composite foreign key corresponds to the order of columns in the composite candidate key in the referenced table. |
General |
system catalog base tables only |
2020-11-06 14:51 |
MIT License |
View |
| 187 |
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 |
View |
| 188 |
Composite foreign keys with an incorrect order of columns (ver 1) |
Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key consist of columns with the same name but the order of columns in the keys is different. For instance, the query returns information about a foreign key (personal_code, country_code) that refers to the candidate key (country_code, personal_code). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause. |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |
| 189 |
Composite foreign keys with an incorrect order of columns (ver 2) |
Find composite foreign keys where the order of columns does not correspond to the order of columns in the referenced candidate key. Find composite foreign keys in case of which the foreign key and candidate key are not the same in terms of data types of the columns. For instance, the query returns information about a foreign key that columns have the types (SMALLINT, INTEGER) that refers to the candidate key that columns have the types (INTEGER, SMALLINT). In SQL keys are ordered sets of columns. Thus, in case of composite foreign key declarations one has to pay attention that the order of columns in the FOREIGN KEY clause matches the order of columns in the REFERENCES clause. |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |
| 190 |
Consistency of CHECK constraint name and content |
Find all CHECK constraints that name contains a digit. Make sure that the name and the Boolean expression are consistent. |
General |
INFORMATION_SCHEMA only |
2021-12-10 13:21 |
MIT License |
View |
| 191 |
Consistency of comments of routines |
Find user-defined routines that have a comment registered by the COMMENT statement and a comment within the routine body. Make sure that there are no inconsistencies between the comments. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2024-12-19 10:53 |
MIT License |
View |
| 192 |
Consistency of using generic column names in candidate key columns that are not foreign key columns |
Find the names on base table columns that are a part of a candidate key but not a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2023-01-14 20:53 |
MIT License |
View |
| 193 |
Consistency of using generic column names in non-candidate key columns that are also not foreign key columns |
Find the names on base table columns that are not a part of a candidate key and a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2023-01-14 20:53 |
MIT License |
View |
| 194 |
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 |
View |
| 195 |
Constraints that are not redefined in a subtable |
Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) but not in its subtable. Unfortunately, PostgreSQL table inheritance is implemented in a manner that some constraints (CHECK, NOT NULL) are inherited from the supertable but others are not. "All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." (PostgreSQL documentation) |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |
| 196 |
Constraints that are not redefined in a subtable and there is no CHECK constraint that compensates this |
Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) but not in its subtable. Additional condition is that in case of the subtable there is no CHECK that permits only one specific value in the constraint column. The presence of such check would make the design acceptable. Unfortunately, PostgreSQL table inheritance is implemented in a manner that some constraints (CHECK, NOT NULL) are inherited from the supertable but others are not. "All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." (PostgreSQL documentation) |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |
| 197 |
Constraints that are not redefined in a subtable but there is a CHECK constraint that compensates this |
Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) but not in its subtable. Exclude constraints where in case of the subtable there is a CHECK that permits only one specific value in the constraint column. The presence of such check would make the design acceptable. Unfortunately, PostgreSQL table inheritance is implemented in a manner that some constraints (CHECK, NOT NULL) are inherited from the supertable but others are not. "All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." (PostgreSQL documentation) |
General |
INFORMATION_SCHEMA+system catalog base tables |
2020-12-25 16:20 |
MIT License |
View |
| 198 |
Constraints that are redefined in a subtable. |
Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) and have been redefined in its subtable. |
General |
system catalog base tables only |
2020-12-02 17:28 |
MIT License |
View |
| 199 |
Constraint-supporting UNIQUE indexes with the same leading column |
Find indexes that support a uniqueness constraint and have the same leading column. |
General |
system catalog base tables only |
2023-11-26 16:01 |
MIT License |
View |
| 200 |
Constraints with the same name within the same schema and constraint type |
Find names of foreign key constraints that are used within the same schema more than once. Find names of check constraints that are used within the same schema more than once. Find names of constraint triggers that are used within the same schema more than once. Different things should have different names. But here different constraints have the same name. Also make sure that this is not a sign of duplication. |
Problem detection |
system catalog base tables only |
2022-11-15 16:43 |
MIT License |
View |