Add 'ON UPDATE/DELETE' clauses to foreign keys.

12 files changed, 45 insertions, 12 deletions

diff --git a/README.md b/README.md
index 79657e3..aeb2db8 100644
--- a/README.md
+++ b/README.md
@@ -214,7 +214,17 @@ curl -X GET -H "Authorization: Bearer L3Q5mvehQeSUNQQbFLfrcUEdEyoknyblXWxlpKkvdl
 ```
 
 to get all the members of a group with the ID
-`8f8d7640-5d51-4445-ad68-7ab217439804` where
+`8f8d7640-5d51-4445-ad68-7ab217439804`
+
+or:
+
+```sh
+curl -X POST "http://localhost:8080/api/oauth2/user/register" \
+    -F "email=a_new@users.email" -F "password=apasswd" \
+    -F "confirm_password=apasswd"
+```
+
+where
 `L3Q5mvehQeSUNQQbFLfrcUEdEyoknyblXWxlpKkvdl` is the token you got in the
 **Request Token** section above.
 
diff --git a/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py b/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py
index 3f72f3e..48bd663 100644
--- a/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py
+++ b/migrations/auth/20221103_02_sGrIs-create-user-credentials-table.py
@@ -13,6 +13,7 @@ steps = [
             user_id TEXT PRIMARY KEY,
             password TEXT NOT NULL,
             FOREIGN KEY(user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS user_credentials")
diff --git a/migrations/auth/20221110_01_WtZ1I-create-resources-table.py b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
index 09ed891..0b769b8 100644
--- a/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
+++ b/migrations/auth/20221110_01_WtZ1I-create-resources-table.py
@@ -15,8 +15,11 @@ steps = [
             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)
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
+            FOREIGN KEY(resource_category_id)
+              REFERENCES resource_categories(resource_category_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS resources")
diff --git a/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py b/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py
index 3174231..0d0eeb9 100644
--- a/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py
+++ b/migrations/auth/20221110_07_7WGa1-create-role-privileges-table.py
@@ -13,8 +13,10 @@ steps = [
             role_id TEXT NOT NULL,
             privilege_id TEXT NOT NULL,
             PRIMARY KEY(role_id, privilege_id),
-            FOREIGN KEY(role_id) REFERENCES roles(role_id),
+            FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS role_privileges"),
diff --git a/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py b/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py
index 7102a57..2048f4a 100644
--- a/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py
+++ b/migrations/auth/20221114_01_n8gsF-create-generic-role-privileges-table.py
@@ -15,8 +15,10 @@ steps = [
             generic_role_id TEXT NOT NULL,
             privilege_id TEXT NOT NULL,
             PRIMARY KEY(generic_role_id, privilege_id),
-            FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id),
+            FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS generic_role_privileges"),
diff --git a/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py b/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py
index e767aeb..6bd101b 100644
--- a/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py
+++ b/migrations/auth/20221114_02_DKKjn-drop-generic-role-tables.py
@@ -24,8 +24,10 @@ steps = [
             generic_role_id TEXT NOT NULL,
             privilege_id TEXT NOT NULL,
             PRIMARY KEY(generic_role_id, privilege_id),
-            FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id),
+            FOREIGN KEY(generic_role_id) REFERENCES generic_roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(privilege_id) REFERENCES privileges(privilege_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """),
     step(
diff --git a/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py b/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py
index f314000..a7e7b45 100644
--- a/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py
+++ b/migrations/auth/20221114_03_PtWjc-create-group-roles-table.py
@@ -13,8 +13,10 @@ steps = [
             group_id TEXT NOT NULL,
             role_id TEXT NOT NULL,
             PRIMARY KEY(group_id, role_id),
-            FOREIGN KEY(group_id) REFERENCES groups(group_id),
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS group_roles"),
diff --git a/migrations/auth/20221114_05_hQun6-create-user-roles-table.py b/migrations/auth/20221114_05_hQun6-create-user-roles-table.py
index d9532dc..e0de751 100644
--- a/migrations/auth/20221114_05_hQun6-create-user-roles-table.py
+++ b/migrations/auth/20221114_05_hQun6-create-user-roles-table.py
@@ -13,8 +13,10 @@ steps = [
             user_id TEXT NOT NULL,
             role_id TEXT NOT NULL,
             PRIMARY KEY(user_id, role_id),
-            FOREIGN KEY(user_id) REFERENCES users(user_id),
+            FOREIGN KEY(user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS user_roles"),
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 2deff10..a4d7806 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
@@ -23,8 +23,10 @@ steps = [
             group_id TEXT NOT NULL,
             role_id TEXT NOT NULL,
             PRIMARY KEY(group_id, role_id),
-            FOREIGN KEY(group_id) REFERENCES groups(group_id),
+            FOREIGN KEY(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """),
     step(
@@ -34,8 +36,10 @@ steps = [
             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(group_id) REFERENCES groups(group_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY(role_id) REFERENCES roles(role_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS group_roles"),
diff --git a/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py b/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py
index 8ab7be4..475be01 100644
--- a/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py
+++ b/migrations/auth/20221219_01_CI3tN-create-oauth2-clients-table.py
@@ -18,6 +18,7 @@ steps = [
             user_id TEXT NOT NULL,
             PRIMARY KEY(client_id),
             FOREIGN KEY(user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS oauth2_clients")
diff --git a/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py b/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py
index 81ca7df..778282b 100644
--- a/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py
+++ b/migrations/auth/20221219_02_buSEU-create-oauth2-tokens-table.py
@@ -21,8 +21,10 @@ steps = [
             expires_in INTEGER NOT NULL,
             user_id TEXT NOT NULL,
             PRIMARY KEY(token_id),
-            FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id),
+            FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY (user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS oauth2_tokens")
diff --git a/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py b/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py
index 495916f..1683f87 100644
--- a/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py
+++ b/migrations/auth/20221219_03_PcTrb-create-authorisation-code-table.py
@@ -21,8 +21,10 @@ steps = [
             code_challenge_method TEXT,
             user_id TEXT NOT NULL,
             PRIMARY KEY (code_id),
-            FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id),
+            FOREIGN KEY (client_id) REFERENCES oauth2_clients(client_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT,
             FOREIGN KEY (user_id) REFERENCES users(user_id)
+              ON UPDATE CASCADE ON DELETE RESTRICT
         ) WITHOUT ROWID
         """,
         "DROP TABLE IF EXISTS authorisation_code")