Filter Queries

Found 89 queries.

Page 1 of 5
  • 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 only

Find all exclude constraints.

General License: MIT (opens in new tab)

#2. All identifying relationships

system catalog base tables only

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 License: MIT (opens in new tab)

#3. All key constraints

system catalog base tables only

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 License: MIT (opens in new tab)

#4. All unique keys have at least one optional column

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#5. All uniqueness constraints where nulls are not distinct

system catalog base tables only

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 License: MIT (opens in new tab)

#6. At most one row is permitted in a table (based on check constraints)

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#7. At most one row is permitted in a table (based on enumeration types)

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#8. Base table has a national identification number as a key

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#9. Base tables that have a surrogate key and all its unique constraints have an optional column

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#10. Base tables that have a surrogate key and do not have any uniqueness constraints

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#11. Base tables that have a unique constraint but not the primary key

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#12. Base tables that have neither a unique constraint nor the primary key

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#13. Base tables that have no uniqueness requirement for rows whatsoever

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#14. Base tables that have only the surrogate key and do not have any other column

INFORMATION_SCHEMA only

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 License: MIT (opens in new tab)

#15. Base tables where uniqueness is achieved by using only unique indexes

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#16. Base tables with exactly one key

system catalog base tables only

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 License: MIT (opens in new tab)

#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 tables

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 () or UNIQUE () constraint can have at most two rows.

Problem detection License: MIT (opens in new tab)

#18. Candidate key columns that have a static default value

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#19. Candidate keys and foreign keys of tables that participate in an inheritance hierarchies

system catalog base tables only

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 License: MIT (opens in new tab)

#20. Candidate keys where all columns have a static default value

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)
# 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 () or UNIQUE () constraint can have at most two rows. 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