auth: Fix tests after enforcing FOREIGN KEY constraint

Fix a number of tests and fixtures that were not conforming to the FOREIGN KEY
constraints:

* Each test that creates a new "object" needs to clean up after itself
* Each fixture that sets up test data needs to clean up after itself

5 files changed, 15 insertions, 4 deletions

diff --git a/migrations/auth/20221110_01_WtZ1I-create-resources-table.py b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
index f99d482..09ed891 100644
--- a/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
+++ b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
@@ -11,9 +11,10 @@ steps = [
         """
         CREATE TABLE IF NOT EXISTS resources(
             group_id TEXT NOT NULL,
-            resource_id TEXT PRIMARY KEY,
+            resource_id TEXT NOT NULL,
             resource_name TEXT NOT NULL,
             resource_category_id TEXT NOT NULL,
+            PRIMARY KEY(group_id, resource_id),
             FOREIGN KEY(group_id) REFERENCES groups(group_id),
             FOREIGN KEY(resource_category_id) REFERENCES resource_categories(resource_category_id)
         ) WITHOUT ROWID
diff --git a/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py b/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py
index 0959116..d3f97ac 100644
--- a/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py
+++ b/migrations/auth/20221110_02_z1dWf-create-mrna-resources-table.py
@@ -13,9 +13,12 @@ steps = [
     step(
         """
         CREATE TABLE IF NOT EXISTS mrna_resources(
+            group_id TEXT NOT NULL,
             resource_id TEXT PRIMARY KEY,
             dataset_id TEXT NOT NULL UNIQUE,
-            FOREIGN KEY(resource_id) REFERENCES resources(resource_id)
+            FOREIGN KEY(group_id, resource_id)
+              REFERENCES resources(group_id, resource_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS mrna_resources")
diff --git a/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py b/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py
index d241f77..7dde67e 100644
--- a/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py
+++ b/migrations/auth/20221110_03_ka3W0-create-phenotype-resources-table.py
@@ -13,10 +13,13 @@ steps = [
     step(
         """
         CREATE TABLE IF NOT EXISTS phenotype_resources(
+            group_id TEXT NOT NULL,
             resource_id TEXT NOT NULL,
             trait_id TEXT NOT NULL UNIQUE,
             PRIMARY KEY(resource_id, trait_id),
-            FOREIGN KEY(resource_id) REFERENCES resources(resource_id)
+            FOREIGN KEY(group_id, resource_id)
+              REFERENCES resources(group_id, resource_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS phenotype_resources")
diff --git a/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py b/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py
index 9200774..ed6e87d 100644
--- a/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py
+++ b/migrations/auth/20221110_04_6PRFQ-create-genotype-resources-table.py
@@ -13,10 +13,13 @@ steps = [
     step(
         """
         CREATE TABLE IF NOT EXISTS genotype_resources(
+            group_id TEXT NOT NULL,
             resource_id TEXT NOT NULL,
             trait_id TEXT NOT NULL UNIQUE,
             PRIMARY KEY(resource_id, trait_id),
-            FOREIGN KEY(resource_id) REFERENCES resources(resource_id)
+            FOREIGN KEY(group_id, resource_id)
+              REFERENCES resources(group_id, resource_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS genotype_resources")
diff --git a/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py b/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py
index 7115bc3..2deff10 100644
--- a/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py
+++ b/migrations/auth/20221117_01_RDlfx-modify-group-roles-add-group-role-id.py
@@ -33,6 +33,7 @@ steps = [
             group_role_id TEXT PRIMARY KEY,
             group_id TEXT NOT NULL,
             role_id TEXT NOT NULL,
+            UNIQUE (group_id, role_id),
             FOREIGN KEY(group_id) REFERENCES groups(group_id),
             FOREIGN KEY(role_id) REFERENCES roles(role_id)
         ) WITHOUT ROWID