| Goal | This query identifies base tables containing two or more DATE or TIMESTAMP (with or without time zone) columns where at least one temporal column is not compared with other temporal columns in a purely temporal multi-column CHECK constraint. Rather than checking only for the complete absence of constraints, it catches partial coverage—for example, if a table defines created_at, updated_at, and archived_at, having CHECK (updated_at >= created_at) still leaves archived_at uncovered and flagged for review. Multiple temporal columns in a table typically represent sequential events or lifecycle milestones that must obey a chronological order. Note that only multi-column CHECK constraints composed exclusively of DATE or TIMESTAMP columns count as valid coverage (constraints mixing non-temporal columns are excluded). The query outputs all temporal columns in each flagged table alongside the uncovered columns and their count. |
|---|---|
| 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
not_covered_columns against all_temporal_columns. Add a multi-column CHECK constraint (e.g., CHECK (archived_at >= created_at)) for any temporal column that must follow a chronological sequence, or confirm that the uncovered column is chronologically independent. 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:
CHECK constraints
Queries of this category provide information about CHECK constraints.
Temporal data
Queries of this category provide information about temporal (time-related) data that is kept in the database.
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 |
|---|---|
| CHECK constraints | Queries of this category provide information about CHECK constraints. |
| Temporal data | Queries of this category provide information about temporal (time-related) data that is kept in the database. |
| 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). |