tree add5050c125618dfb2e26b817924a3da39a9b302
parent 58f7d955f1815c0d56a5b064315f9505f40ef925
author Jonathan Amsterdam <jba@google.com> 1629920636 -0400
committer Jonathan Amsterdam <jba@google.com> 1629923928 +0000

migrations: add foreign key constraint on units.path_id

Make new DB operations enforce that every value of path_id in the
units table refers to a valid id in the paths table.

The omission of ON DELETE CASCADE is deliberate. We do not want to
delete a row from units when a row from paths is removed; that would
remove data if a row in paths was removed, and instead we want to
signal an error if that happens.

Don't check the constraint for existing rows, because it takes a long
time and locks the table. We can do that later, after cleaning up
violations, with

    ALTER TABLE units VALIDATE CONSTRAINT units_path_id_fkey;

Change-Id: If0cc34b502b12177618d47830c1bb8989f290bea
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/345112
Trust: Jonathan Amsterdam <jba@google.com>
Run-TryBot: Jonathan Amsterdam <jba@google.com>
Reviewed-by: Julie Qiu <julie@golang.org>
TryBot-Result: kokoro <noreply+kokoro@google.com>