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

1 files changed, 4 insertions, 1 deletions

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")