Oracle 外鍵約束實戰：主鍵建立、外鍵關聯、級聯刪除與異常處理完整範例

業務場景

部門表 `MID_A_TAB` (主表) 與員工表 `MID_B_TAB` (子表)，部門刪除時自動刪除該部門下所有員工 (級聯刪除)。

DDL 腳本全集

1. 建表

CREATE TABLE mid_a_tab (
  a      VARCHAR2(20),
  b      VARCHAR2(10),
  detpno VARCHAR2(10)   -- 部門編號 (主鍵)
) TABLESPACE users;

CREATE TABLE mid_b_tab (
  a      VARCHAR2(20),
  b      VARCHAR2(10),
  deptno VARCHAR2(10)   -- 部門編號 (外鍵)
) TABLESPACE users;

2. 建立主鍵

-- 主表主鍵
ALTER TABLE mid_a_tab ADD CONSTRAINT pk_mid_a_tab PRIMARY KEY (detpno);

-- 子表主鍵 (業務主鍵，非外鍵)
ALTER TABLE mid_b_tab ADD CONSTRAINT pk_mid_b_tab PRIMARY KEY (a);

3. 建立外鍵 + 級聯刪除

ALTER TABLE mid_b_tab
ADD CONSTRAINT fk_mid_b_tab_deptno
FOREIGN KEY (deptno)
REFERENCES mid_a_tab (detpno)
ON DELETE CASCADE;  -- 關鍵：主表刪除行 → 自動刪除子表關聯行

驗證測試

-- 1. 插入主表部門
INSERT INTO mid_a_tab (a, b, detpno) VALUES ('1', '1', '10');
-- 1 row created.

-- 2. 插入子表員工 (引用部門 10)
INSERT INTO mid_b_tab (a, b, deptno) VALUES ('EMP001', 'Zhang', '10');
INSERT INTO mid_b_tab (a, b, deptno) VALUES ('EMP002', 'Li', '10');
COMMIT;

-- 3. 刪除主表部門 10
DELETE FROM mid_a_tab WHERE detpno = '10';
-- 1 row deleted.

-- 4. 驗證子表員工已級聯刪除
SELECT * FROM mid_b_tab WHERE deptno = '10';
-- no rows selected  (級聯生效)

-- 5. 嘗試插入不存在部門的員工 (應報錯)
INSERT INTO mid_b_tab (a, b, deptno) VALUES ('EMP003', 'Wang', '99');
-- ORA-02291: integrity constraint (FK_MID_B_TAB_DEPTNO) violated - parent key not found

外鍵約束行為模式對比

效能與維護關鍵點

1. 外鍵列必須建索引：`CREATE INDEX idx_mid_b_tab_deptno ON mid_b_tab(deptno);`。無索引時，主表刪除/更新需 **全表掃描子表** 檢查關聯行，並持有 **全表鎖 (TM Lock)** 導致嚴重併發阻塞。
2. 級聯刪除會遞歸觸發：若子表又是其他表的主表，級聯會層層傳遞，**小心炸彈表** (刪一行觸發百萬行刪除)。
3. DDL 變更需謹慎：`DROP TABLE` 主表需先 `DROP` 子表或 `DROP CONSTRAINT`，否則報錯。
4. 禁用/啟用約束**
5. `ALTER TABLE mid_b_tab DISABLE CONSTRAINT fk_mid_b_tab_deptno;` (大量載入數據時提速)
6. `ALTER TABLE mid_b_tab ENABLE CONSTRAINT fk_mid_b_tab_deptno;` (載入後啟用，可選 `VALIDATE`/`NOVALIDATE`)