From 31fe447112b6b3d76b0612df91f3eca2e47f2961 Mon Sep 17 00:00:00 2001
From: Santo Cariotti <santo@dcariotti.me>
Date: Fri, 18 Oct 2024 10:42:13 +0200
Subject: Use only one position per user

---
 schema/init.sql | 9 +++++++--
 1 file changed, 7 insertions(+), 2 deletions(-)

(limited to 'schema/init.sql')

diff --git a/schema/init.sql b/schema/init.sql
index df76581..5f4fd4f 100644
--- a/schema/init.sql
+++ b/schema/init.sql
@@ -20,6 +20,8 @@ CREATE TABLE positions(
     PRIMARY KEY(id),
     CONSTRAINT fk_users_id
         FOREIGN KEY(user_id) REFERENCES users(id)
+        ON DELETE CASCADE,
+    CONSTRAINT unique_user_position UNIQUE(user_id)
 );
 
 CREATE TYPE level_alert AS ENUM ('One', 'Two', 'Three');
@@ -34,8 +36,9 @@ CREATE TABLE alerts(
     text3 text NOT NULL,
     reached_users INTEGER DEFAULT 0 NOT NULL,
     PRIMARY KEY(id),
-    CONSTRAINT fk_users_id
+    CONSTRAINT fk_users_ich 
         FOREIGN KEY(user_id) REFERENCES users(id)
+        ON DELETE CASCADE
 );
 
 CREATE TABLE notifications(
@@ -47,7 +50,9 @@ CREATE TABLE notifications(
     created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
     PRIMARY KEY(id),
     CONSTRAINT fk_alerts_id
-        FOREIGN KEY(alert_id) REFERENCES alerts(id),
+        FOREIGN KEY(alert_id) REFERENCES alerts(id)
+        ON DELETE CASCADE,
     CONSTRAINT fk_positions_id
         FOREIGN KEY(position_id) REFERENCES positions(id)
+        ON DELETE CASCADE
 );
-- 
cgit v1.2.3-71-g8e6c

