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>
diff --git a/migrations/000143_add_units_paths_fk.down.sql b/migrations/000143_add_units_paths_fk.down.sql
new file mode 100644
index 0000000..bba721a
--- /dev/null
+++ b/migrations/000143_add_units_paths_fk.down.sql
@@ -0,0 +1,9 @@
+-- Copyright 2021 The Go Authors. All rights reserved.
+-- Use of this source code is governed by a BSD-style
+-- license that can be found in the LICENSE file.
+
+BEGIN;
+
+ALTER TABLE units DROP CONSTRAINT units_path_id_fkey;
+
+END;
diff --git a/migrations/000143_add_units_paths_fk.up.sql b/migrations/000143_add_units_paths_fk.up.sql
new file mode 100644
index 0000000..fb7bb20
--- /dev/null
+++ b/migrations/000143_add_units_paths_fk.up.sql
@@ -0,0 +1,12 @@
+-- Copyright 2021 The Go Authors. All rights reserved.
+-- Use of this source code is governed by a BSD-style
+-- license that can be found in the LICENSE file.
+
+BEGIN;
+
+ALTER TABLE units
+ ADD CONSTRAINT units_path_id_fkey
+ FOREIGN KEY (path_id) REFERENCES paths(id)
+ NOT VALID;
+
+END;
