| Seq nr | Name▲ | Goal | Type | Data source | Last update | License | ... |
|---|---|---|---|---|---|---|---|
| 1 | All exclude constraints | Find all exclude constraints. | General | system catalog base tables only | 2021-12-16 11:21 | MIT License | |
| 2 | All identifying relationships | Find all non-identifying relationships where the foreign key (a set of columns) is a subset of a uniqueness constraint (primary key, unique, or exclude constraint). | General | system catalog base tables only | 2023-11-01 11:32 | MIT License | |
| 3 | All key constraints | Find all the primary key and unique constraints of base tables. | General | system catalog base tables only | 2021-10-16 10:37 | MIT License | |
| 4 | 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 | |
| 5 | At most one row is permitted in a table (based on check constraints) | Find base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. | General | INFORMATION_SCHEMA+system catalog base tables | 2022-11-03 15:21 | MIT License | |
| 6 | At most one row is permitted in a table (based on enumeration types) | Find base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. | General | INFORMATION_SCHEMA+system catalog base tables | 2022-11-03 15:18 | MIT License | |
| 7 | Base table has a national identification number as a key | Find base table columns that name refers to the possibility that these are used to register national identification numbers (personal codes). Find the columns that constitute a key of a table. This in turn enforces a (restrictive) business rule that all persons of interest come from one country. Make sure that the enforced constraint is valid, i.e., there is indeed such rule in the domain. | General | INFORMATION_SCHEMA+system catalog base tables | 2020-11-06 14:51 | MIT License | |
| 8 | 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 | |
| 9 | 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 | |
| 10 | Base tables that have a unique constraint but not the primary key | A common style is to declare in each base table one of the candidate keys as the primary key. All the other candidate keys would be alternate keys that will be enforce with the help of UNIQUE + NOT NULL constraints. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:30 | MIT License | |
| 11 | Base tables that have neither a unique constraint nor the primary key | Find base tables without any unique constraints and the primary key. In such tables there are no restrictions for recording duplicate rows. Each row represents a true proposition about the real world. It does not make the proposition truer if one presents it more than once. Moreover, duplicate rows increase data size. Without keys the DBMS lacks vital information about data in the database that it can internally use to choose better execution plans and in this way improve performance of database operations. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables. | Problem detection | INFORMATION_SCHEMA only | 2021-02-25 17:30 | MIT License | |
| 12 | Base tables that have no uniqueness requirement for rows whatsoever | Find base tables without any unique constraints and primary key as well as any unique index, whether it is created explicitly by a developer or automatically by the DBMS. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-10-21 10:16 | MIT License | |
| 13 | Base tables that have only the surrogate key and do not have any other column | Do not create unnecessary tables. If a table has cardinality 1 (one column), then most probably the values in this column should not be system generated unique values. | Problem detection | INFORMATION_SCHEMA only | 2021-03-08 00:41 | MIT License | |
| 14 | Base tables where uniqueness is achieved by using only unique indexes | Find base tables where uniqueness is achieved by using only unique indexes, i.e., there is at least one unique index but no uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-10-21 01:54 | MIT License | |
| 15 | Base tables with exactly one key | Find all base tables that have exactly one PRIMARY KEY or UNIQUE constraint. Find and enforce all the keys. Are you sure there are not more keys in the table? | General | system catalog base tables only | 2021-10-16 10:39 | MIT License | |
| 16 | BOOLEAN base table and foreign table columns with a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves olnly this column | Find base table columns with the Boolean type that has a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves only this column. Avoid unnecessary constraints. It is quite improbable that there must be such constraints. For instance, a table with PRIMARY KEY ( | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2022-11-03 10:46 | MIT License | |
| 17 | Candidate key columns that have a static default value | Find base table columns that are covered by a primary key or a unique constraint and that probably have a static default value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-12 11:32 | MIT License | |
| 18 | Candidate keys and foreign keys of tables that participate in an inheritance hierarchies | Find primary key, unique, foreign key, and exclude constraints that have been defined in tables that participate in an inheritance hierarchy. Do not forget to redefine the constraints that are defined on supertables also on their subtables. | General | system catalog base tables only | 2020-11-06 14:51 | MIT License | |
| 19 | Candidate keys where all columns have a static default value | Find base table primary key and unique constraints where all columns probably have a static default value. Candidate key columns (columns that belong to the primary key or to an unique constraints) shouldn't have static default values unless you are enforcing a rule that a table can have at most one row. The point of default values is that system assigns them automatically. There cannot be multiple rows with the same key value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-11-12 11:33 | MIT License | |
| 20 | Case sensitive and case insensitive uniqueness of the same simple key | Find sets of columns that have both case sensitive and case insesitive unique constraints enforced based on these columns. In case of textual columns uniqueness could be checked either in case sensitive or case insensitive way. It is a contradiction to check the uniqueneness in both ways in case of the same key. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:29 | MIT License |