mysql~資料完整性考慮~外來鍵約束

张占岭發表於2024-05-09
MySql

在MySQL中，當為表新增外來鍵約束時，可以指定在刪除或更新父表記錄時的行為。下面進行總結：

  1. CASCADE：當父表中的記錄被刪除或更新時，自動刪除或更新子表中相關聯的記錄。這意味著如果父表中的記錄被刪除，那麼相應的子表中與之關聯的記錄也會被刪除。

  2. SET NULL：當父表中的記錄被刪除或更新時，子表中的外來鍵列會被設定為NULL。這意味著如果父表中的記錄被刪除，子表中的外來鍵列將會被置空。

  3. SET DEFAULT：當父表中的記錄被刪除或更新時，子表中的外來鍵列會被設定為預設值。這意味著如果父表中的記錄被刪除，子表中的外來鍵列將會被設定為預先定義的預設值。

  4. NO ACTION：當父表中的記錄被刪除或更新時，不採取任何動作。這意味著如果存在父表和子表之間的關聯，但是父表中的記錄被刪除或更新時，在子表中不會發生任何變化，操作將會被拒絕。

  5. RESTRICT: 當嘗試刪除或者更新父表中有關聯子表資料時，如果子表中存在對應的外來鍵關聯資料，則不允許操作父表資料

這些選項可以根據實際需求來選擇，以確保資料的完整性和一致性。

其中，RESTRICT我再詳細說明一下其作用如下：

  1. DELETE RESTRICT：當嘗試刪除父表中有關聯子表資料時，如果子表中存在對應的外來鍵關聯資料，則不允許刪除父表資料，會丟擲一個錯誤。這樣可以確保資料的完整性，避免因刪除父表資料而導致子表資料無法正確關聯。

  2. UPDATE RESTRICT：當嘗試更新父表中被外來鍵引用的列的值時，如果子表中存在對應的外來鍵關聯資料，則不允許更新父表資料，會丟擲一個錯誤。這也是為了保證資料的完整性，防止因更新父表資料而導致子表資料關聯出現問題。

例項

  • 建立兩張表user_info和user_ext，然後user_ext中的user_id是user_info的id的外來鍵，並使用restrict方式，如果要更新或者刪除user_info中的資料時，保護了user_ext已經引用的資料。
CREATE TABLE `user_info`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `user_ext`  (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  CONSTRAINT `user_ext_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

當刪除主表資料（user_ext中已經存在引用的資料），會出現這個提示，不讓你刪除主表資料，要想刪除，需要先刪子表的才行

當然，如果希望在刪除主鍵資料時，同時自動將子表資料刪除，就可以在配置外來鍵時，選擇cascade即可

相關文章