From cae45e130891cf97f93d743ace381eb8209df2a8 Mon Sep 17 00:00:00 2001
From: Arda Serdar Pektezol <1669855+pektezol@users.noreply.github.com>
Date: Wed, 12 Jul 2023 09:56:11 +0300
Subject: fix: unique relation for map history and routes

Former-commit-id: f3ea2ce6784973385d63980b6680294bbcee0188
---
 backend/database/history.sql | 2 +-
 backend/database/init.sql    | 6 ++++--
 backend/database/route.sql   | 2 +-
 3 files changed, 6 insertions(+), 4 deletions(-)

(limited to 'backend')

diff --git a/backend/database/history.sql b/backend/database/history.sql
index d7c4999..320d72f 100644
--- a/backend/database/history.sql
+++ b/backend/database/history.sql
@@ -27,7 +27,7 @@ INSERT INTO map_history(map_id,category_id,user_name,score_count,record_date) VA
 (15,1,'Tyronis',2,'2011-10-05 00:00:00'),
 (16,1,'LookLikeAKango',2,'2011-10-05 00:00:00'),
 (16,1,'jyjey',0,'2012-08-25 00:00:00'),
-(17,1,'rocoty',0,'2011-10-05 00:00:00'),
+(17,1,'rocoty',2,'2011-10-05 00:00:00'),
 (17,1,'Nidboj132',0,'2023-02-05 00:00:00'),
 -- 3
 (18,1,'The Last Tofus',5,'2011-05-08 00:00:00'),
diff --git a/backend/database/init.sql b/backend/database/init.sql
index c7d098c..196cb71 100644
--- a/backend/database/init.sql
+++ b/backend/database/init.sql
@@ -49,7 +49,8 @@ CREATE TABLE map_routes (
   showcase TEXT NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (map_id) REFERENCES maps(id),
-  FOREIGN KEY (category_id) REFERENCES categories(id)
+  FOREIGN KEY (category_id) REFERENCES categories(id),
+  UNIQUE (map_id, category_id, score_count)
 );
 
 CREATE TABLE map_history (
@@ -61,7 +62,8 @@ CREATE TABLE map_history (
   record_date TIMESTAMP NOT NULL,
   PRIMARY KEY (id),
   FOREIGN KEY (category_id) REFERENCES categories(id),
-  FOREIGN KEY (map_id) REFERENCES maps(id)
+  FOREIGN KEY (map_id) REFERENCES maps(id),
+  UNIQUE (map_id, category_id, score_count)
 );
 
 CREATE TABLE map_ratings (
diff --git a/backend/database/route.sql b/backend/database/route.sql
index 6f090c7..f26180e 100644
--- a/backend/database/route.sql
+++ b/backend/database/route.sql
@@ -27,7 +27,7 @@ INSERT INTO map_routes(map_id,category_id,score_count,description,showcase) VALU
 (15,1,2,'',''),
 (16,1,2,'',''),
 (16,1,0,'',''),
-(17,1,0,'',''),
+(17,1,2,'',''),
 (17,1,0,'',''),
 -- 3
 (18,1,5,'',''),
-- 
cgit v1.2.3