自動增量鍵和外鍵作為關係數據庫的組合
除了玩工作台並試圖了解我需要如何為項目設置此數據庫之外，我沒有任何數據庫經驗。我一直在搜尋網際網路，我越來越困惑我將如何做我想做的事。以下是我想要完成的。
我需要一個保存患者資訊的 MySQL 數據庫。患者資訊將是姓名、id（唯一）等以及每個測試的所有測試和样本。
- 每個患者可以進行多項測試
- 每個測試可以有多個樣本
- 如果我刪除了一個病人，那麼所有的測試和样本都會被刪除
- 如果我刪除一個測試，那麼所有樣本都將被刪除
- 如果我刪除了一項測試的所有樣本或一名患者的所有測試，則不應刪除該測試或患者。
到目前為止，我了解關係數據庫是我需要的。我也知道我可以使用外鍵來連結每個表的患者 ID 以及每個表的測試 ID。我還得出結論，我想要數據完整性，這樣我就不必手動管理刪除和更新行。這也不允許孤立行。我相信這可以通過複合鍵來完成，但這是我開始對如何設置它感到困惑的地方。
我希望它能夠像這樣工作（每一行都是一個插入語句）：
患者表：
|-------------------| |Patient ID |Name | |-------------------| |12345 |ANG | |54321 |JUE | |-------------------|測試表：
|----------------------| |Test ID |Patient ID | |----------------------| |1 |12345 | |2 |12345 | |3 |12345 | |1 |54321 | |2 |54321 | |4 |12345 | |----------------------|樣品表：
|----------------------------------| |Sample ID |Test ID |Patient ID | |----------------------------------| |1 |1 |12345 | |2 |1 |12345 | |3 |1 |12345 | |1 |2 |12345 | |1 |1 |54321 | |1 |2 |54321 | |2 |2 |54321 | |2 |2 |12345 | |----------------------------------|這可以輕鬆完成嗎？我也知道這可以通過一兩個觸發器來完成，但我知道不處理刪除等。還有另一種更容易實現的方法嗎？此外，從這個數據庫寫入和讀取的數據由我的 LabVIEW 程序處理。
謝謝喬爾、格林斯通、斯托萊格。經過更多研究，你們的評論和答案幫助我弄清楚了我需要什麼。事實證明，當涉及到觸發器和數據完整性時，我想多了。
回顧一下需要什麼：
病床：無
測試表：對於每個唯一的外鍵 (
Patient ID)，Test ID將從 1 開始範例表：對於每個唯一的複合外鍵 (
Test IDANDPatient ID)，Sample ID將從 1 開始。
- 每個患者可以進行多項測試
- 每個測試可以有多個樣本
- 如果我刪除了一個病人，那麼所有的測試和样本都會被刪除
- 如果我刪除一個測試，那麼所有樣本都將被刪除
- 如果我刪除了一項測試的所有樣本或一名患者的所有測試，則不應刪除該測試或患者。
回答：
這很容易通過觸發器實現並保持數據完整性。就像 Joel 所說，數據是一個非常直接的結構，其中可以是多對一
samples、test多對一tests，並且只有在表中是patient唯一的。這允許使用級聯來防止孤兒並保持數據完整性。設置級聯滿足所有必需的項目符號。喬爾的例子：patients``patient... CONSTRAINT 'FK_TEST__PATIENT' FOREIGN KEY ('patient_id') REFERENCES 'PATIENT' ('patient_id') ON DELETE CASCADE ... CONSTRAINT 'FK_SAMPLE__TEST' FOREIGN KEY ('test_id') REFERENCES 'TEST' ('test_id') ON DELETE CASCADE ...下一部分是為每個唯一鍵或複合鍵重置自動增量（這不是內置的自動增量）。這可以通過使用
BEFORE INSERT觸發器來完成。（歸功於德瓦特）這是我為我的
test表修改的觸發器：delimiter $$ CREATE TRIGGER `insert_test_auto_inc` BEFORE INSERT ON `tests` FOR EACH ROW BEGIN SELECT COALESCE(MAX(`Test ID`) + 1, 1) INTO @`Test ID` FROM tests WHERE `Patient ID` = NEW.`Patient ID`; SET NEW.`Test ID` = @`Test ID`; END $$觸發程式碼如何工作，從
MAX函式開始。Null 的最大值為 Null，Null + 1 為 Null。該COALESCE函式返回第一個非 Null 值，因此如果Test ID未定義，則為逗號後的 1。如果Test ID是一個值，比如 1，那麼它會將其加到 1，因此 1 + 1 = 2。該INTO部分將該值放入使用者變數@Test ID中。最大值僅適用於匹配的行Patient ID。這個自動遞增的（通過程式碼，不是內置的）值被NEW寫入Test ID.這是我為我的
sample表修改的觸發器：delimiter $$ CREATE TRIGGER `insert_sample_auto_inc` BEFORE INSERT ON `samples` FOR EACH ROW BEGIN SELECT COALESCE(MAX(`Sample ID`) + 1, 1) INTO @`Sample ID` FROM samples WHERE `Patient ID` = NEW.`Patient ID` AND `Test ID` = NEW.`Test ID`; SET NEW.`Sample ID` = @`Sample ID`; END $$同樣的事情發生在範例觸發器中，只是它現在同時匹配
Patient ID並Test ID使用WHERE語句。以下是創建此範例數據庫所需的所有命令，該範例數據庫的工作方式與此問題一樣。此程式碼是使用 MySQL Workbench 生成的。
delimiter $$ CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */$$ delimiter $$ CREATE TABLE `patients` ( `Patient ID` int(10) unsigned NOT NULL, `First Name` varchar(45) DEFAULT NULL, `Last Name` varchar(45) DEFAULT NULL, `DOB` varchar(45) DEFAULT NULL, PRIMARY KEY (`Patient ID`), UNIQUE KEY `PatientID_UNIQUE` (`Patient ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8$$ delimiter $$ CREATE TABLE `tests` ( `Test ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Patient ID` int(10) unsigned NOT NULL, PRIMARY KEY (`Test ID`,`Patient ID`), KEY `fk_tests_patient_id_idx` (`Patient ID`), CONSTRAINT `fk_tests_patient_id` FOREIGN KEY (`Patient ID`) REFERENCES `patients` (`Patient ID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8$$ CREATE DEFINER=`root`@`localhost` TRIGGER `testdb`.`insert_test_auto_inc` BEFORE INSERT ON `testdb`.`tests` FOR EACH ROW -- Edit trigger body code below this line. Do not edit lines above this one BEGIN SELECT COALESCE(MAX(`Test ID`) + 1, 1) INTO @`Test ID` FROM tests WHERE `Patient ID` = NEW.`Patient ID`; SET NEW.`Test ID` = @`Test ID`; END $$ delimiter $$ CREATE TABLE `samples` ( `Sample ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `Test ID` int(10) unsigned NOT NULL, `Patient ID` int(10) unsigned NOT NULL, `Count Value` int(11) DEFAULT NULL, PRIMARY KEY (`Sample ID`,`Test ID`,`Patient ID`), KEY `fk_samples_test_id_idx` (`Test ID`), KEY `fk_samples_patient_id_idx` (`Patient ID`), CONSTRAINT `fk_samples_patient_id` FOREIGN KEY (`Patient ID`) REFERENCES `patients` (`Patient ID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_samples_test_id` FOREIGN KEY (`Test ID`) REFERENCES `tests` (`Test ID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8$$ CREATE DEFINER=`root`@`localhost` TRIGGER `testdb`.`insert_sample_auto_inc` BEFORE INSERT ON `testdb`.`samples` FOR EACH ROW -- Edit trigger body code below this line. Do not edit lines above this one BEGIN SELECT COALESCE(MAX(`Sample ID`) + 1, 1) INTO @`Sample ID` FROM samples WHERE `Patient ID` = NEW.`Patient ID` AND `Test ID` = NEW.`Test ID`; SET NEW.`Sample ID` = @`Sample ID`; END $$ delimiter ;
在您的案例中，您不需要復合鍵來強制執行您的參照完整性。原因是您有一個非常簡單的三層層次結構：
PATIENT + | ^ TEST + | ^ SAMPLE你的
SAMPLE表只需要一個簡單的外鍵到你的TEST表，你的TEST表只需要一個簡單的外鍵到你的PATIENT表。這是有效的，因為每個樣本記錄都需要一個測試記錄，而每個測試都需要一個病人。如果您刪除患者，則必須級聯刪除他們的測試。如果你刪除一個測試，它的樣本必須被級聯刪除。因此，您不需要
patient_id在TEST表中。您只需要在外鍵上聲明級聯刪除，就像這樣……... CONSTRAINT 'FK_TEST__PATIENT' FOREIGN KEY ('patient_id') REFERENCES 'PATIENT' ('patient_id') ON DELETE CASCADE ... CONSTRAINT 'FK_SAMPLE__TEST' FOREIGN KEY ('test_id') REFERENCES 'TEST' ('test_id') ON DELETE CASCADE ...