| 541 |
NOT NULL constraint via CHECK instead of NOT NULL constraint |
Find columns where the NOT NULL constraint has been added with the help of an explicit CHECK constraints instead of a NOT NULL constraint. One should note that internally NOT NULL constraints are treated as a kind of CHECK constraints. Nevertheless, the design should be consistent (i.e., NOT NULL is enforced with the same way in different places). |
Problem detection |
INFORMATION_SCHEMA only |
2024-12-12 09:42 |
MIT License |
View |
| 542 |
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 |
| 543 |
NOT VALID foreign key constraints |
Find not valid foreign key constraints. These constraints have been created so that the existing data has not been checked against the constraint. It could be deliberate in case of legacy systems that have data quality problems. However, ideally all the data in the table conforms to the constraint. |
Problem detection |
system catalog base tables only |
2021-02-25 17:30 |
MIT License |
View |
| 544 |
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 |
View |
| 545 |
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 |
View |
| 546 |
Number of rows in base tables |
Find the number of rows in base tables. |
General |
system catalog base tables only |
2022-10-27 19:44 |
MIT License |
View |
| 547 |
Number of system-generated and user-defined constraint names by constraint type (constraints that involve more than one column) |
Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. |
Sofware measure |
system catalog base tables only |
2023-01-10 14:53 |
MIT License |
View |
| 548 |
Number of system-generated and user-defined constraint names by constraint type (constraints that involve one column) |
Find the number of system-generated constraint names by constraint type. Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. |
Sofware measure |
system catalog base tables only |
2023-01-10 14:52 |
MIT License |
View |
| 549 |
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 |
View |
| 550 |
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 |
View |
| 551 |
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 |
View |
| 552 |
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 |
View |
| 553 |
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-12-13 19:41 |
MIT License |
View |
| 554 |
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 |
View |
| 555 |
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 |
View |
| 556 |
ON DELETE CASCADE is probably missing (based on the multiplicity of the relationship) |
Find foreign key constraints that completely overlap with a candidate key constraint (primary key or unique constraint) but the foreign key constraint does not have the ON DELETE CASCADE compensating action. In this case there is a foreign key that implements a relationship type between a strong entity type and a weak entity type (1-1 relationship type). Therefore, in this case ON DELETE CASCADE is an appropriate compensating action. |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |
| 557 |
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 |
| 558 |
ON DELETE SET NULL is probably missing |
Find implementations of the adjacency list design pattern, where the corresponding foreign key columns are optional but the foreign key constraint does not have ON DELETE SET NULL compensating action. Implement adjacency list correctly. |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |
| 559 |
One-to-one relationships |
Find one-to-one relationships between base tables. In this case the foreign key columns must have primary key or unique constraint. These tables could implement inheritance hierarchy that has been specified in the conceptual data model. |
General |
system catalog base tables only |
2023-01-06 13:39 |
MIT License |
View |
| 560 |
One true lookup table |
Find tables that contain all (or most) of the classifier values and tables that refer to these. |
Problem detection |
system catalog base tables only |
2021-02-25 17:29 |
MIT License |
View |