Elevate not-null CHECK constraints to `NOT NULL` column constraints
See #358222 (closed) for the background
Summary
https://docs.gitlab.com/ee/development/database/not_null_constraints.html#add-a-not-null-constraint-to-an-existing-column documents how to add a not-null CHECK
constraint to an existing column. We leverage the ability to validate the constraint at a later point, which allows us to do this in a non-blocking fashion.
PG12 introduced the ability to add column-level NOT NULL
constraints without re-validation if a not-null CHECK
constraint is present.
In general, NOT NULL
column level constraints are preferred over custom CHECK
constraints - many tools understand column-level constraints and a column constraint more explicitly documents the data model.
Proposal
Extend below migration helper to elevate the constraint to the column level:
validate_not_null_constraint :epics, :description
- Current behavior: Validate the
CHECK
constraint - Added behavior, inside transaction: Once it's valid, add a column-level
NOT NULL
constraint - Added behavior, inside transaction: Drop the
CHECK
constraint
This has to be done with a v2 migration helper implementation, so that existing migrations are not seeing this change in behavior.
Alternatively, we can also provide another migration helper method to support steps (2) and (3) - although this further complicates matters for developers.