Filter Queries
- All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
- Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets, it shouldn't be a problem in case of the relatively small amount of data in the system catalog of a database.
#1. All exclude constraints
system catalog base tables onlyFind all exclude constraints.
#2. All identifying relationships
system catalog base tables onlyFind 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).
#3. All key constraints
system catalog base tables onlyThis query retrieves a comprehensive list of all PRIMARY KEY and UNIQUE constraints defined on base tables within the database. These constraints are the fundamental mechanisms for enforcing entity integrity and uniqueness. The output provides essential information for schema auditing, documentation generation, and analyzing the data model's key structures. It allows administrators and developers to quickly verify how uniqueness is enforced for each table.
#4. All unique keys have at least one optional column
INFORMATION_SCHEMA+system catalog base tablesThis query identifies base tables where every unique key (whether enforced by a UNIQUE constraint or a unique index) includes at least one optional (nullable) column. This is a critical design flaw because it allows rows to exist where the identifying values are partially or entirely missing. Furthermore, because standard SQL treats NULL as an unknown state rather than a distinct value, two NULLs are not considered equal. As a result, a standard UNIQUE constraint fails to prevent the insertion of multiple identical rows if they contain NULL values. The query exempts constraints that explicitly use the UNIQUE NULLS NOT DISTINCT clause, which safely treats NULLs as equal.
#5. All uniqueness constraints where nulls are not distinct
system catalog base tables onlyThis query identifies UNIQUE constraints that explicitly use the NULLS NOT DISTINCT clause. Introduced in PostgreSQL 15, this modifier changes standard SQL behavior by treating NULL values as equal, thereby preventing multiple identical rows that contain NULLs. This query is useful for auditing the schema to review where this specific null-handling logic is applied, ensuring it aligns with business rules, or flagging it for environments that require compatibility with older PostgreSQL versions
#6. At most one row is permitted in a table (based on check constraints)
INFORMATION_SCHEMA+system catalog base tablesFind 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.
#7. At most one row is permitted in a table (based on enumeration types)
INFORMATION_SCHEMA+system catalog base tablesFind 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.
#8. Base table has a national identification number as a key
INFORMATION_SCHEMA+system catalog base tablesThis query identifies base table columns whose names suggest they store national identification numbers (personal codes), specifically targeting those that serve as a primary or unique key. Using a national ID as a standalone key enforces a highly restrictive business rule: it assumes that all individuals in the system come from a single country, as identical ID numbers can exist in different countries. The query flags these instances to prompt a review, ensuring that this single-country limitation is intentional and actually aligns with the domain rules.
#9. Base tables that have a surrogate key and all its unique constraints have an optional column
INFORMATION_SCHEMA+system catalog base tablesThis query identifies tables with a surrogate primary key (an auto-generated, meaningless single-column identifier) that either lack a natural key entirely or have a flawed natural key constraint. A common design mistake when introducing a surrogate key is forgetting to enforce uniqueness on the actual business identifiers (natural keys). Furthermore, if a UNIQUE constraint includes an optional (nullable) column, it fails to prevent duplicate rows. Because NULL evaluates to an unknown state and is not considered equal to another NULL in standard SQL, multiple rows with identical values and NULLs can be inserted. The query flags these vulnerabilities unless the constraint explicitly uses the UNIQUE NULLS NOT DISTINCT clause, which safely treats NULL values as equal.
#10. Base tables that have a surrogate key and do not have any uniqueness constraints
INFORMATION_SCHEMA+system catalog base tablesThis query identifies tables that use a single-column surrogate primary key but lack any other UNIQUE constraints or unique indexes. The absence of additional unique constraints suggests that the natural business key has not been enforced, creating a risk of data duplication that violates business rules. Tables consisting of only a single column are excluded from this check.
#11. Base tables that have a unique constraint but not the primary key
INFORMATION_SCHEMA+system catalog base tablesThis query identifies leaf base tables (tables with no inheriting children) that possess one or more UNIQUE constraints but lack a defined PRIMARY KEY. It is a standard database design best practice to designate one candidate key as the primary key for every table, treating remaining candidate keys as alternate keys. Parent tables are specifically excluded from this check because, in PostgreSQL inheritance, primary keys do not enforce global uniqueness across the entire table hierarchy. Parent tables often serve as abstract templates, meaning primary keys are properly defined only on their respective child tables.
#12. Base tables that have neither a unique constraint nor the primary key
INFORMATION_SCHEMA+system catalog base tablesThis query identifies leaf base tables (tables with no inheriting children) that completely lack both PRIMARY KEY and UNIQUE constraints. It is a standard database design best practice to define at least one candidate key and designate it as the primary key for every table. Parent tables are specifically excluded from this check because, in PostgreSQL inheritance, primary keys do not enforce global uniqueness across the entire table hierarchy. Instead, parent tables often serve as abstract templates, meaning primary keys are properly defined only on their respective child tables.
#13. Base tables that have no uniqueness requirement for rows whatsoever
INFORMATION_SCHEMA+system catalog base tablesFind 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.
#14. Base tables that have only the surrogate key and do not have any other column
INFORMATION_SCHEMA onlyDo 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.
#15. Base tables where uniqueness is achieved by using only unique indexes
INFORMATION_SCHEMA+system catalog base tablesFind 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)
#16. Base tables with exactly one key
system catalog base tables onlyFind 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?
#17. BOOLEAN base table and foreign table columns with a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves olnly this column
INFORMATION_SCHEMA+system catalog base tablesFind 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 (
#18. Candidate key columns that have a static default value
INFORMATION_SCHEMA+system catalog base tablesFind base table columns that are covered by a primary key or a unique constraint and that probably have a static default value.
#19. Candidate keys and foreign keys of tables that participate in an inheritance hierarchies
system catalog base tables onlyFind 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.
#20. Candidate keys where all columns have a static default value
INFORMATION_SCHEMA+system catalog base tablesFind 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.
| # | Name(sorted ascending, activate to sort descending) | Goal(activate to sort ascending) | Type(activate to sort ascending) | Data source(activate to sort ascending) | Last update(activate to sort ascending) | License | Actions |
|---|---|---|---|---|---|---|---|
| 1 | All exclude constraints | Find all exclude constraints. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 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 | MIT (opens in new tab) | View | |
| 3 | All key constraints | This query retrieves a comprehensive list of all PRIMARY KEY and UNIQUE constraints defined on base tables within the database. These constraints are the fundamental mechanisms for enforcing entity integrity and uniqueness. The output provides essential information for schema auditing, documentation generation, and analyzing the data model's key structures. It allows administrators and developers to quickly verify how uniqueness is enforced for each table. | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 4 | All unique keys have at least one optional column | This query identifies base tables where every unique key (whether enforced by a UNIQUE constraint or a unique index) includes at least one optional (nullable) column. This is a critical design flaw because it allows rows to exist where the identifying values are partially or entirely missing. Furthermore, because standard SQL treats NULL as an unknown state rather than a distinct value, two NULLs are not considered equal. As a result, a standard UNIQUE constraint fails to prevent the insertion of multiple identical rows if they contain NULL values. The query exempts constraints that explicitly use the UNIQUE NULLS NOT DISTINCT clause, which safely treats NULLs as equal. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 5 | All uniqueness constraints where nulls are not distinct | This query identifies UNIQUE constraints that explicitly use the NULLS NOT DISTINCT clause. Introduced in PostgreSQL 15, this modifier changes standard SQL behavior by treating NULL values as equal, thereby preventing multiple identical rows that contain NULLs. This query is useful for auditing the schema to review where this specific null-handling logic is applied, ensuring it aligns with business rules, or flagging it for environments that require compatibility with older PostgreSQL versions | General | system catalog base tables only | MIT (opens in new tab) | View | |
| 6 | 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 | MIT (opens in new tab) | View | |
| 7 | 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 | MIT (opens in new tab) | View | |
| 8 | Base table has a national identification number as a key | This query identifies base table columns whose names suggest they store national identification numbers (personal codes), specifically targeting those that serve as a primary or unique key. Using a national ID as a standalone key enforces a highly restrictive business rule: it assumes that all individuals in the system come from a single country, as identical ID numbers can exist in different countries. The query flags these instances to prompt a review, ensuring that this single-country limitation is intentional and actually aligns with the domain rules. | General | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 9 | Base tables that have a surrogate key and all its unique constraints have an optional column | This query identifies tables with a surrogate primary key (an auto-generated, meaningless single-column identifier) that either lack a natural key entirely or have a flawed natural key constraint. A common design mistake when introducing a surrogate key is forgetting to enforce uniqueness on the actual business identifiers (natural keys). Furthermore, if a UNIQUE constraint includes an optional (nullable) column, it fails to prevent duplicate rows. Because NULL evaluates to an unknown state and is not considered equal to another NULL in standard SQL, multiple rows with identical values and NULLs can be inserted. The query flags these vulnerabilities unless the constraint explicitly uses the UNIQUE NULLS NOT DISTINCT clause, which safely treats NULL values as equal. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 10 | Base tables that have a surrogate key and do not have any uniqueness constraints | This query identifies tables that use a single-column surrogate primary key but lack any other UNIQUE constraints or unique indexes. The absence of additional unique constraints suggests that the natural business key has not been enforced, creating a risk of data duplication that violates business rules. Tables consisting of only a single column are excluded from this check. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 11 | Base tables that have a unique constraint but not the primary key | This query identifies leaf base tables (tables with no inheriting children) that possess one or more UNIQUE constraints but lack a defined PRIMARY KEY. It is a standard database design best practice to designate one candidate key as the primary key for every table, treating remaining candidate keys as alternate keys. Parent tables are specifically excluded from this check because, in PostgreSQL inheritance, primary keys do not enforce global uniqueness across the entire table hierarchy. Parent tables often serve as abstract templates, meaning primary keys are properly defined only on their respective child tables. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 12 | Base tables that have neither a unique constraint nor the primary key | This query identifies leaf base tables (tables with no inheriting children) that completely lack both PRIMARY KEY and UNIQUE constraints. It is a standard database design best practice to define at least one candidate key and designate it as the primary key for every table. Parent tables are specifically excluded from this check because, in PostgreSQL inheritance, primary keys do not enforce global uniqueness across the entire table hierarchy. Instead, parent tables often serve as abstract templates, meaning primary keys are properly defined only on their respective child tables. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | MIT (opens in new tab) | View | |
| 13 | 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 | MIT (opens in new tab) | View | |
| 14 | 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 | MIT (opens in new tab) | View | |
| 15 | 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 | MIT (opens in new tab) | View | |
| 16 | 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 | MIT (opens in new tab) | View | |
| 17 | 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 | MIT (opens in new tab) | View | |
| 18 | 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 | MIT (opens in new tab) | View | |
| 19 | 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 | MIT (opens in new tab) | View | |
| 20 | 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 | MIT (opens in new tab) | View |