| Goal | This query identifies optional (nullable) base table columns that participate in a UNIQUE constraint or index, but do not participate in a FOREIGN KEY constraint. In database design, tables use candidate keys to uniquely identify rows; one acts as the primary key, while the others serve as alternate keys. To properly enforce a standalone alternate key, all its columns should be mandatory (NOT NULL). Columns involved in foreign keys are excluded from this check, as optional relationships legitimately require nullable foreign keys. The query also exempts constraints that explicitly use the UNIQUE NULLS NOT DISTINCT clause, which safely treats NULL values as equal. |
|---|---|
| Notes | Refers to the column pg_index.indnullsnotdistinct and thus works starting from PostgreSQL 15. |
| Type | Problem detection Each row in the result could represent a flaw in the design |
| Reliability | Medium Medium number of false-positive results |
| License | MIT (opens in new tab) |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Fixing Suggestion
Collections
This query belongs to the following collections:
Find problems about integrity constraints
A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
| Name | Description |
|---|---|
| Find problems about integrity constraints | A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview |
| Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Categories
This query is classified under the following categories:
Missing data
Queries of this category provide information about missing data (NULLs) in a database.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.
Validity and completeness
Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).
| Name | Description |
|---|---|
| Missing data | Queries of this category provide information about missing data (NULLs) in a database. |
| Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |
| Validity and completeness | Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness). |