| 301 |
Columns of derived tables that name has been given by the system (2) |
Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system. |
Problem detection |
system catalog base tables only |
2023-12-21 12:17 |
MIT License |
View |
| 302 |
Columns that have the same name as some domain/type |
Use different names to avoid confusion. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2022-11-28 14:47 |
MIT License |
View |
| 303 |
Columns that have the same name as their domain/type |
Find the columns that name is the same as the name of the type of the column or the domain of the column. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2022-11-30 13:04 |
MIT License |
View |
| 304 |
Columns with BOOLEAN type that do not have a good name |
The phrase "is_" or "has_" or "can_" (in English) or "on_" (in Estonian) should be used in the name - preferably in the beginning. Worse: agreed, kinnitatud. Better: contract_is_agreed, leping_on_kinnitatud. Perhaps the best: is_contract_agreed or is_agreement, on_leping_kinnitatud, on_kinnitatud. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2024-01-03 09:56 |
MIT License |
View |
| 305 |
Columns with exact/floating numeric types have textual default values |
The default value of a column should belong to the type of the column. The system shouldn't conduct unnecessary type casts. |
Problem detection |
INFORMATION_SCHEMA only |
2021-02-25 17:29 |
MIT License |
View |
| 306 |
Columns with only one value |
Find base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:29 |
MIT License |
View |
| 307 |
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 |
| 308 |
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 |
| 309 |
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 |
| 310 |
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 |
| 311 |
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 |
| 312 |
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 |
| 313 |
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 |
| 314 |
Cycle in a hierarchy |
There should not be cycles in hierarchies meaning that the parent must always be specified. In this case a parent must reference to a child or to itself, otherwise it cannot be registered. |
Problem detection |
system catalog base tables only |
2021-02-25 17:30 |
MIT License |
View |
| 315 |
Cycles in relationships |
Find as to whether foreign key constraints between tables form a cycle. For instance, if table C refers to table B, table B refers to table A, table A refers to table C and all the foreign key columns are mandatory, then one cannot add data to the tables. Is cycle value in the result should not contain value TRUE. One should check manually as to whether the foreign key columns are mandatory or optional. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-28 15:15 |
MIT License |
View |
| 316 |
Database can be accessed through PUBLIC privileges |
Find as to whether the database access by users (applications) can take place thanks to PUBLIC privileges, i.e., find as to whether PUBLIC (all current and future users) has CONNECT privilege in the database. PUBLIC gets the privilege by default. |
Problem detection |
system catalog base tables only |
2022-10-31 10:19 |
MIT License |
View |
| 317 |
Database connect privilege is missing |
Find non-superusers who have a privilege to use a table or a routine but do not have the privilege to connect to the database. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-21 13:51 |
MIT License |
View |
| 318 |
Database objects of the same type and case insensitive name in the same container |
Find database objects with the same type and case insensitive name in the same container. It can only happen if some of the names are case insensitive and others are case sensitive. For instance, the same schema can contain the table "Client" and client |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2024-12-21 17:13 |
MIT License |
View |
| 319 |
Database object that belong to the public interface (virtual data layer) and that names contain the letters õäöüÕÄÖÜ |
Find database object that belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make it more difficult to use the interface by other programs. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |
| 320 |
Database object that do not belong to the public interface (virtual data layer) and that names contain the letters õäöüÕÄÖÜ |
Find database object that do not belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make the naming style inconsistent with the naming style of elements that belong to the public interface. If applications access base tables directly, then the letters can cause the same problems as in case of derived tables, i.e., applications may have difficulties with such names. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |