From 14f48b4e4f7463e0972c2a5b39e94ee674ec93ba Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Wed, 7 Aug 2024 23:21:47 +0900
Subject: feat(backend): add unique constraint to testcase_results

---
 backend/schema.sql | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

(limited to 'backend/schema.sql')

diff --git a/backend/schema.sql b/backend/schema.sql
index 53c68ec..7188d72 100644
--- a/backend/schema.sql
+++ b/backend/schema.sql
@@ -73,6 +73,7 @@ CREATE TABLE testcase_results (
     stderr             TEXT        NOT NULL,
     created_at         TIMESTAMP   NOT NULL DEFAULT NOW(),
     CONSTRAINT fk_submission_id FOREIGN KEY(submission_id) REFERENCES submissions(submission_id),
-    CONSTRAINT fk_testcase_id FOREIGN KEY(testcase_id) REFERENCES testcases(testcase_id)
+    CONSTRAINT fk_testcase_id FOREIGN KEY(testcase_id) REFERENCES testcases(testcase_id),
+    CONSTRAINT uq_submission_id_testcase_id UNIQUE(submission_id, testcase_id)
 );
 CREATE INDEX idx_testcase_results_submission_id ON testcase_results(submission_id);
-- 
cgit v1.2.3-70-g09d2

