finalize votes table columns with nullable foreign keys and unique index

1 files changed, 20 insertions, 0 deletions

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;