From 60fd8a68ed87cd7352b490e88ceff3ec21855937 Mon Sep 17 00:00:00 2001
From: Alexander Kavon <me+git@alexkavon.com>
Date: Wed, 24 Jan 2024 05:49:33 -0500
Subject: finalize votes table columns with nullable foreign keys and unique
 index

---
 migrations/006_create_votes_table.sql | 20 ++++++++++++++++++++
 1 file changed, 20 insertions(+)
 create mode 100644 migrations/006_create_votes_table.sql

(limited to 'migrations/006_create_votes_table.sql')

diff --git a/migrations/006_create_votes_table.sql b/migrations/006_create_votes_table.sql
new file mode 100644
index 0000000..8ed4c5f
--- /dev/null
+++ b/migrations/006_create_votes_table.sql
@@ -0,0 +1,20 @@
+CREATE TABLE votes (
+    id SERIAL PRIMARY KEY,
+    post_id INT REFERENCES posts(id) ON DELETE CASCADE,
+    comment_id INT REFERENCES comments(id) ON DELETE CASCADE,
+    user_id INT NOT NULL,
+    inc INT NOT NULL CHECK (inc in (-1, 1)),
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    CONSTRAINT vote_pkey UNIQUE NULLS NOT DISTINCT (post_id, comment_id, user_id),
+    CHECK ((post_id IS NOT NULL AND comment_id IS NULL) OR (comment_id IS NOT NULL AND post_id IS NULL))
+);
+
+CREATE TRIGGER set_timestamp
+BEFORE UPDATE ON votes
+FOR EACH ROW
+EXECUTE PROCEDURE trigger_set_timestamp();
+
+---- create above / drop below ----
+
+DROP TABLE votes;
-- 
cgit v1.2.3

