Add helper for creating not null constraints for multiple columns
What does this MR do and why?
Adds migration helpers to assist in managing CHECK
constraints that ensure one (or more) of a set of columns is present.
As we progress with adding sharding keys to many tables, we will come across several that require multiple sharding keys. The most common examples of this are tables that can belong to a group or a project (see the final example in the linked docs).
Usually, sharding keys must be NOT NULL
(and have a constraint to enforce this). When there are two possible keys there must also be a constraint, but there is not yet a recommended way to accomplish this. The current schema has usages of the following:
CHECK ((project_id IS NOT NULL) OR (group_id IS NOT NULL))
CHECK ((project_id IS NULL) <> (group_id IS NULL))
CHECK (num_nonnulls(project_id, group_id) = 1)
The helper added in this merge request, add_multi_column_not_null_constraint
, uses num_nonnulls
as in the final point above. This was chosen over the others because it allows greater control over the desired number of NOT NULL
values (an exact number by default, or >
/<
/etc by specifying a custom operator
).
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
Before | After |
---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.