Skip to content

Add helper for creating not null constraints for multiple columns

Tiger Watson requested to merge num-nonnulls-constraint-helper into master

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.

Edited by Tiger Watson

Merge request reports