| # | Name | Goal | Type | Data source | Last update ▼ | License | |
|---|---|---|---|---|---|---|---|
| 341 | Updatable views that have not been turned to read only | Find views that are theoretically updatable but do not have INSTEAD OF trigger or DO INSTEAD NOTHING rule to prevent data modifications through the view. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-29 10:20 | MIT License | View |
| 342 | ON DELETE CASCADE is probably not needed (based on the relationship type) | Find foreign key constraints that implement a non-identifying relationship type and have ON DELETE CASCADE compensating action. If the identity of the parent table is not a part of the identity of the child table, then there is a non-identifying relationship type and most probably the foreign key should not have ON DELETE CASCADE. | Problem detection | system catalog base tables only | 2023-10-28 18:38 | MIT License | View |
| 343 | Inconsistent means of concatenation in various database objects | Find as to different database objects use different means to concatenate text (format function, concat function, concat_ws function, || operator). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-28 17:36 | MIT License | View |
| 344 | Overlapping non-function based indexes that have the same leading column but with different operator class | Find non-function based indexes (both unique and non-unique) that have identical first column but the operator class that is used in case of the first column is different. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. | General | system catalog base tables only | 2023-10-28 15:05 | MIT License | View |
| 345 | Non-updatable views with DO INSTEAD NOTHING rules | Find non-updatable views that have a DO INSTEAD NOTHING rule. The rule is used to prevent updates. However, the view is aniway non-updatable. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-28 13:01 | MIT License | View |
| 346 | The size of base tables and their indexes | Find the size of base tables without indexes, size of the indexes of the table, total size of the table (including its indexes) and percentage of the index size from the total size. If the size of indexes of a table is relatively high, then check as to whether all the indexes are needed. | General | system catalog base tables only | 2023-10-27 20:38 | MIT License | View |
| 347 | Inconsistent use of gratuitous context in the names of non-foreign key and non-candidate key columns | Find the number of names on base table columns that are not a part of a candidate key and a foreign key and that contain the name of the table. Find the number of names on base table columns that are not a part of a candidate key and a foreign key and that do not contain the name of the table. Make sure that table name is used consistently in the column names. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-26 20:25 | MIT License | View |
| 348 | Base tables that have a surrogate key and do not have any uniqueness constraints | A surrogate key is a key that consist of one column. The values of this column do not have any meaning for the user and the system generates the values (integers) automatically. In case of defining a surrogate key in a table it is a common mistake to forget declaring existing natural keys in the table. The query discards tables with only one column. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-26 17:47 | MIT License | View |
| 349 | Inconsistent use of functions and procedures | Find as to whether the database has both procedures as well as functions that do not return a value (i.e., return VOID). Such routines are generally used to modify data in the database. The support of procedures was added to PostgreSQL 11. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-26 15:05 | MIT License | View |
| 350 | All unique keys have at least one optional column | Find base tables where all unique keys (sets of columns covered by a unique constraint, or a unique index) have at least one optional column. In this case there can be rows in the table where the values that should identify the row are missing. Because NULL is not a value and is not duplicate of another NULL the, follwing is possible: CREATE TABLE Uniq(a INTEGER NOT NULL, b INTEGER, CONSTRAINT ak_uniq UNIQUE (a, b)); INSERT INTO Uniq(a, b) VALUES (1, NULL); INSERT INTO Uniq(a, b) VALUES (1, NULL); |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-21 11:54 | MIT License | View |
| 351 | Base tables that have a surrogate key and all its unique constraints have an optional column | A surrogate key is a primary key that consist of one column. The values of this column do not have any meaning for the user and the system usually generates the values (integers) automatically. In case of defining a surrogate key in a table it is a common mistake to forget declaring existing natural keys in the table. If a key covers an optional column then it does not prevent duplicate rows where some values are missing and other values are equal. Because NULL is not a value and is not duplicate of another NULL the, follwing is possible: CREATE TABLE Uniq(a INTEGER NOT NULL, b INTEGER, CONSTRAINT ak_uniq UNIQUE (a, b)); INSERT INTO Uniq(a, b) VALUES (1, NULL); INSERT INTO Uniq(a, b) VALUES (1, NULL); |
Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-21 11:54 | MIT License | View |
| 352 | Regular expression with possibly a LIKE pattern | Find expressions that use a regular expression with a like predicate pattern. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-21 11:11 | MIT License | View |
| 353 | LIKE with a regular expression pattern | Find expressions that use LIKE (including ILIKE) predicate with a regular expression patterns. In a LIKE pattern one can use only _ and % metasymbols to construct a pattern. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-21 10:38 | MIT License | View |
| 354 | NOT NULL domains | Find domains with NOT NULL constraints and base table columns that have been defined based on the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments. | General | INFORMATION_SCHEMA+system catalog base tables | 2023-10-20 19:10 | MIT License | View |
| 355 | NOT NULL constraint is directly associated with a column instead of the domain of the column | Find mandatory (NOT NULL) base table columns that have been defined based on the same domain but the NOT NULL constraint is associated directly with the column not to the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-20 15:34 | MIT License | View |
| 356 | Comments of columns | Find all comments of columns of tables. | General | system catalog base tables only | 2023-10-18 14:09 | MIT License | View |
| 357 | Sequences that are not owned by a table column | Find sequence generators that are not owned by a table column, i.e., if one drops the table or the column, then the sequence generator stays in place. | Problem detection | system catalog base tables only | 2023-10-06 14:40 | MIT License | View |
| 358 | ON UPDATE CASCADE is probably missing (based on data types) | Find foreign key constraints where the foreign key column does not have an integer type or uuid type and the foreign key constraint does not have ON UPDATE CASCADE compensating action. In this case the foreign key probably refferes to a natural key (i.e., a key that values have meaning outside the computer system) and ON UPDATE CASCADE would be suitable because the key values could be changed over time. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-06 14:19 | MIT License | View |
| 359 | Base table columns permitting negative prices/quantity | Find non-foreign key base table columns that name refers to the possibility that these are used to register prices/quantities. Find the columns that do not have any simple CHECK constraints, i.e., a constraint that covers only this column. In this case registration of negative price/quantity is most probably not prohibited. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-06 14:14 | MIT License | View |
| 360 | Perhaps check constraint names contain incorrect or unnecessary words | Find names of check constraints (either associated with a base table or a domain) that names contain words that are not needed in the name. For instance, constraints cannot ensure the correctness of data and thus the word correct should not be used in the names. Words like "valid" or phrases like "follows_rules" are just noise because all the constraint ensure that the registered data values are valid and follow certain rules. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-06 14:10 | MIT License | View |