| Goal | This query identifies columns where non-nullability is enforced using an explicit CHECK constraint (e.g., CHECK (column_name IS NOT NULL)) rather than a standard NOT NULL column constraint. Although PostgreSQL internally processes NOT NULL constraints in a manner similar to CHECK constraints, relying on explicit CHECK constraints for this purpose violates standard SQL conventions. Enforcing non-nullability consistently via the dedicated NOT NULL constraint improves schema readability, ORM compatibility, and overall design uniformity. |
|---|---|
| Notes | The query considers both constraints that are associated with a domain as well as constraints that are directly attached to a table. |
| 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 only |
| SQL Query |
|
Fixing Suggestion
To resolve this issue and standardize the schema, drop the explicit CHECK constraint and apply a standard NOT NULL constraint to the column. You can achieve this by running ALTER TABLE [table_name] ALTER COLUMN [column_name] SET NOT NULL;, followed by dropping the redundant check constraint with ALTER TABLE [table_name] DROP CONSTRAINT [check_constraint_name];.
Collections
This query belongs to the following collections:
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 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.
Domains
Queries of this category provide information about reusable specifications of column properties.
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Missing data
Queries of this category provide information about missing data (NULLs) in a database.
| Name | Description |
|---|---|
| CHECK constraints | Queries of this category provide information about CHECK constraints. |
| Domains | Queries of this category provide information about reusable specifications of column properties. |
| Inconsistencies | Queries of this catergory provide information about inconsistencies of solving the same problem in different places. |
| Missing data | Queries of this category provide information about missing data (NULLs) in a database. |