限制具有復雜驗證邏輯的匯總表的插入/更新
我正在設計一些 MySQL 表，並且遇到了一個我認為可以通過唯一性約束解決的問題，但不確定如何解決。我的實際案例複雜而令人困惑，因此我將其簡化為一個有意義的類比，涉及學校、教室和考試成績（但**請注意：**這不是家庭作業——我是一名試圖解決現實世界問題的開發人員）。
背景故事的一些領域實體和業務邏輯：
- 有
Students- 學生拿
Classes- 在
Classes你得到Tests哪些是分級的- 每個
Quarter學生都必須註冊一個特定CourseLoad的課程，這是一組預先確定的課程。ACourseLoad是一組同時進行的特定Classes課程**（**例如，核心數學課程負載可能包括：MATH 205、MATH 210和MATH 326等）這是我目前的表格，雖然我 100% 願意重構它們，但我對給定的設計非常滿意（另請注意：請不要在這裡停留在命名約定上，它的設計是最重要的！）：
students ======== student_id : INTEGER PRIMARY KEY AUTO INCREMENT (hereafter 'IPKAI') student_name : VARCHAR(100) student_dob : DATETIME classes ======= class_id : IPKAI class_name : VARCHAR(50) // Ex: 'CHEM 101' courseloads =========== courseload_id : IPKAI courseload_name : VARCHAR(50) // Ex: 'Core Math Courseload', 'General Ed Courseload', etc. // Classes and Courseloads are many-to-many: The same Class can appear inside multiple // Courseloads, and the same Courseload can consist of many Classes classes_to_courseloads ====================== classes_to_courseload_id : IPKAI class_id : FK INT (classes) courseload_id : FK INT (courseloads) quarters ======== quarter_id : IPKAI student_id : FK INT (students) courseload_id : FK INT (courseloads) tests ===== test_id : IPKAI student_id : FK INT (students) quarter_id : FK INT (quarters) class_id : FK INT (classes) test_date : DATETIME test_grade : INT所以最後，我的問題：
A
Test發生在給定的Quarter（報告期）內。ATest也屬於 aClass，它本身屬於 1+CourseLoads。一個給定的Quarter也有一個CourseLoad分配給它（因為學生必須選擇CourseLoad他們要解決那個特定季度的問題）。**我擔心這裡的驗證。**讓我們舉一個活生生的例子來說明這一點：
給定
Quarter一個quarter_id=14…A報名參加“核心數學課程”
Student（）student_id=508``courseload_id=5核心數學 CourseLoad 由 3 部分組成
Classes：
- 數學 205 (
class_id=8); 和- 數學 210 (
class_id=18); 和- 數學 326 (
class_id=92)在該季度的某個時間點，學生參加了 MATH 210 的期中
test_id=38321考試，學生的成績為 94鑑於所有這些資訊，
tests這個中期的記錄將如下所示：test_id student_id quarter_id class_id test_date test_grade ============================================================================ 38321 508 14 18 2015-07-23 94**我需要驗證的是：學生不可能在給定的季度獲得不屬於該季度課程量的課程的測試記錄。**換一種說法：
test_id student_id quarter_id class_id test_date test_grade ============================================================================ 38326 508 14 21 2015-08-19 85
tests…應該是無效條目，如果我們嘗試將其作為新記錄插入，則應該違反某些約束或引發錯誤。因為，class_id=21不是核心數學課程負載的一部分*。*我在這裡尋找一個優雅的解決方案。同樣，我認為這可以通過將 UK 引入
tests表中來解決，但如果這意味著正確解決問題，我會考慮任何解決方案（甚至是重表重構）。
這種驗證不適用於列或表級別的約束。根據您與該數據庫的互動方式，您有兩種選擇：
- 驗證是通過“前端”界面完成的，它不會讓你做出“壞”的選擇。然後，您可以在 SQL 查詢中再次驗證它們，以仔細檢查是否需要。
- 驗證只發生在 SQL 查詢中，允許“前端”傳遞潛在的“壞”數據。
如果您選擇在 SQL 查詢中進行驗證，您可以使用典型的 IF THEN 迭代邏輯建構顯式檢查，也可以將其建構到您的 INSERT 查詢中。要執行後者，您的表需要包含模仿您的驗證方案的關係中的數據，幸運的是它看起來像您的表一樣。像下面這樣的東西應該做你正在尋找的東西（@表示變數，我假設你將它們傳遞或提前設置它們）：
INSERT INTO Tests (student_id, quarter_id, class_id, test_date, test_grade) SELECT student_id ,quarter_id ,class_id ,test_date ,test_grade FROM (SELECT @student_id AS student_id ,@quarter_id AS quarter_id ,@class_id AS class_id ,@test_date AS test_date ,@test_grade AS test_grade ) AS InsertSet INNER JOIN quarters Q ON InsertSet.quarter_id = Q.quarter_ID AND InsertSet.student_ID = Q.student_ID INNER JOIN classes_to_courseloads CTC ON Q.courseload_ID = CTC.coarseload_ID AND InsertSet.class_id = CTC.class_id通過將變數集轉換為內聯表，我們可以連接其他表以確保數據有效。我們 INNER JOIN 到 Quarters 表以確保 Quarter_ID 和 student_ID 匹配，從而驗證他是否註冊了該季度。然後我們 INNER JOIN 到 Classes_to_Courseloads 以驗證測試成績所針對的 class_ID 是否與他在 Quarters 表中註冊的 student_ID 的 courseLoad_ID 匹配。如果該查詢中的任何 JOIN 失敗，您將不會得到任何結果，因此不會將任何內容插入到 Tests 表中。
至於返回消息或錯誤，您可以使用變數對測試執行 SELECT 查詢以嘗試返回 test_ID，如果您得到結果，則 INSERT 成功，如果您沒有失敗。