| 981 |
The number of user-defined triggers by schema, by type, and in total |
Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives an indication about the state of enforcing constraints at the database level. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 982 |
The number of user-defined triggers |
Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives some indications about the state of enforcing constraints at the database level. The query does not count internal triggers. |
Sofware measure |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 983 |
Do not leave out the referential constraints (islands) |
Try to find missing foreign key constraints. Find base tables that do not participate in any referential constraint (as the referenced table or as the referencing table). These tables are like "islands" in the database schema. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 984 |
Do not leave out the referential constraints (based on column names) (2) |
Try to find missing foreign key constraints. Find columns of base tables that are not a part of any primary key, unique, and foreign key constraint, but have a name that reffers to the possibility that these are used to record references to a user. Exclude columns that have the default value CURRENT_USER or SESSION_USER. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 985 |
Do not leave out the referential constraints (based on column names) |
Try to find missing foreign key constraints. Find columns of base tables that are not a part of any primary key, unique, and foreign key constraint, do not have an associated sequence generator, but have a name that reffers to the possibility that these are used to record some kind of codes or id's. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 986 |
Do not leave out referential constraints (based on composite keys) |
Try to find missing foreign key constraints. Find columns of base tables that are not covered by any foreign key constraint but belong to a composite key, do not have an associated sequence generator, and have a name that refers to the possibility that these are used to record some kind of codes or id's. Moreover, there must be at least one other base table that has a column with the same name. Such strategy would find missing constraints in tables that implement many-to-many relationship types but which that are not complete "islands" in terms of missing foreign key constraints. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 987 |
Do not leave out the referential constraints (based on adjacency list design) |
Try to find missing foreign key constraints. Find non-key and non-foreign key columns of base tables that do not have an associated sequence generator, and that name refers to the possibility that the column holds parent identifiers. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 988 |
Do not leave out the referential constraints (pairs of tables) |
Try to find missing foreign key constraints. Find pairs of base table columns that have the similar name, perhaps the same type, and that are not associated through a foreign key relationship. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 989 |
Extensions that are available but are not installed |
Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 990 |
Installed extensions |
Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 991 |
Routine body only in uppercase |
Uppercase means screaming and having code entirely in uppercase makes its reading more difficult. On the other hand, it would be a good idea to have keywords in uppercase. Find routines that body contains a SQL data manipulation statement (which shouldn't be entirely in uppercase) but still the body is completely in uppercase. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 992 |
IS DISTINCT FROM should be used instead of <> in WHEN clauses |
Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-07 10:11 |
MIT License |
View |
| 993 |
Perhaps IS DISTINCT FROM should be used instead of <> in WHEN clauses |
Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs. |
General |
INFORMATION_SCHEMA only |
2025-11-07 10:11 |
MIT License |
View |
| 994 |
Names of database objects that mix snake_case and camelCase/PascalCase |
Use consistent style of naming. Prefer snake_case. Regular identifiers are stored in the PostgreSQL system catalog in lowercase. Thus, if you use, for instance the identifier thisIsLongTableName, then, for instance,in the pg_dump result you will see the table name thisislongtablename. If the name in the system catalog is thisIsLongTableName, then it means that the name is a delimited identifier, i.e., case sensitive. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 995 |
Columns that have the same name as some domain/type |
Use different names to avoid confusion. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 996 |
Domain name and type name are the same |
Use different names to avoid confusion. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 997 |
User-defined routines that use positional references to parameters |
Use parameter names instead of positional references to improve code evolvability. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 998 |
Privileges to use base tables |
Users (applications) should ideally use a database through virtual data layer and thus not directly use base tables. If there is a need to provide direct access to the base tables, then one should grant access based on the principle of least privilege, i.e., to the minimum possible number of base tables. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 999 |
Using conditionals to determine the returned value |
Use SQL language instead of PL/pgSQL where possible. Instead of using an IF statement, you can check as to whether the data modification succeeded or not by using the RETURNING clause in the data modification statement. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1000 |
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 |
2025-11-07 10:11 |
MIT License |
View |