| 501 | NOT IN or <> ALL in routines | Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-10-25 16:42 | MIT License | |
| 502 | 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 | |
| 503 | 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 | |
| 504 | Number of columns covered with constraints | For different types of constraints find the number of columns covered with constraints of such type. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2021-10-16 11:01 | MIT License | |
| 505 | Number of derived tables that aggregate data | Find the number of derived tables that aggregate data. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2024-01-14 12:33 | MIT License | |
| 506 | Number of tables covered by derived tables | Find the number of base tables, the number of base tables that are referred from at least one derived table (view or materialized view), the number of base tables that are referred from at least one view, and the number of base tables that are referred from at least one materialized view. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2023-11-11 09:33 | MIT License | |
| 507 | Number of underlying tables of derived tables | Find for each view or materialized view the number of tables based on which the derived table has been directly defined. These tables could be base tables or derived tables. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2023-11-11 09:30 | MIT License | |
| 508 | Number of used tables | Find statistics about how many derived tables have how many different underlying tables. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2024-01-14 13:43 | MIT License | |
| 509 | Number of using views | Find statistics about how many base tables have how many derived tables that use these tables. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2024-01-14 13:42 | MIT License | |
| 510 | Number of views with and without security barrier | Find the number of views, the number of views with and without security barrier setting, and the names of views with and without the security barrier setting. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2024-01-14 12:54 | MIT License | |
| 511 | Numeric literals between apostrophes | Placing numeric literals between apostrophes will cause unnecessary type conversions. It could also be that the literal should indeed be textual but the problem is in choosing the values. For instance, table Occupation has column occupation_code with the type VARCHAR(3). However, all the values in the column consist of digits (for instance, 1, 2, 3). Thus, it would have been better to a) use SMALLINT as the column type or b) use different occupation codes that contain additional symbols to digits. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 13:20 | MIT License | |
| 512 | ON DELETE CASCADE is not needed (based on classifier tables) | Find foreign key constraints with ON DELETE CASCADE compensating action that refer to classifier (reference data) tables. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-12-08 14:52 | MIT License | |
| 513 | Only ID primary key | Find base base tables have the simple primary key that contains a column with the (case insensitive) name id and an integer type. In addition, the primary key values are generated automatically by the system by using a sequence generator. In addition the base table must not have any unique constraint. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-11-09 15:15 | MIT License | |
| 514 | Only one value permitted in a base table or a foreign table column (based on check constraints) | Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. The constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable or is used to enfore the rule that the table can have at most one row. | General | INFORMATION_SCHEMA+system catalog base tables | 2024-04-30 20:14 | MIT License | |
| 515 | Only one value permitted in a base table or a foreign table column (based on enumeration types) | Find columns of base tables or foreign tables in case of which the type of the column permits only one value in the column. The type is an enumeration type that specifies only one value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |
| 516 | Only one value permitted in a non-inherited base table or a foreign table column (based on check constraints) | Find columns of base tables or foreign tables in case of which a check constraint on the column permits only one value in the column. Exclude columns that are inherited from a supertable because the constraint may be correct if it is applied to a column of a subtable that is inherited from the supertable. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-04-30 20:16 | MIT License | |
| 517 | ON UPDATE CASCADE is not needed (based on surrogate keys) | Find foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE in case of foreign keys that reference to surrogate keys. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-04-30 18:39 | MIT License | |
| 518 | 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 | |
| 519 | ON UPDATE CASCADE is probably missing (based on the properties of the referenced column) | Find the foreign key constraints that do not have ON UPDATE CASCADE and that referenced key is a simple key that has an integer type, is not covered by another foreign key, and does not have an associated sequence generator, i.e., the foreign key references a simple natural key. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-04-30 18:39 | MIT License | |
| 520 | Optional base table columns that participate in a UNIQUE constraint or index | Find optional base table columns that participate in a UNIQUE constraint or index. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-10-21 15:57 | MIT License | |