關鍵點：**Oracle 僅支持級聯刪除 (ON DELETE CASCADE)，不支持級聯更新 (ON UPDATE CASCADE)**。SQL Server 兩者均支持。若業務需主鍵值變更自動傳播至子表，Oracle 需通過觸發器或應用層邏輯實現。
-- 父表
CREATE TABLE departments (
dept_id NUMBER(4) PRIMARY KEY,
dept_name VARCHAR2(30) NOT NULL
);
-- 子表：定義外鍵並啟用 ON DELETE CASCADE
CREATE TABLE employees (
emp_id NUMBER(6) PRIMARY KEY,
emp_name VARCHAR2(50) NOT NULL,
dept_id NUMBER(4),
salary NUMBER(8,2),
CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE CASCADE -- 核心：刪除部門時自動刪除該部門所有員工
);
-- 三層級聯：訂單 → 訂單項 → 發貨明細
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
cust_id NUMBER,
order_date DATE DEFAULT SYSDATE
);
CREATE TABLE order_items (
item_id NUMBER PRIMARY KEY,
order_id NUMBER,
product_id NUMBER,
quantity NUMBER,
CONSTRAINT fk_item_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
ON DELETE CASCADE
);
CREATE TABLE shipments (
ship_id NUMBER PRIMARY KEY,
item_id NUMBER,
ship_date DATE,
qty NUMBER,
CONSTRAINT fk_ship_item
FOREIGN KEY (item_id)
REFERENCES order_items(item_id)
ON DELETE CASCADE
);
-- 刪除一筆訂單，關聯的所有項與發貨記錄自動級聯刪除
| 動作模式 | 觸發條件 | 子表外鍵列行為 | Oracle 支持 | 適用場景 |
|---|---|---|---|---|
| RESTRICT (默認) | 父表主鍵被 UPDATE/DELETE | 阻止操作，拋出 ORA-02292 | ✅ 支持 | 嚴格保護主數據，防止誤刪 |
| SET NULL | 父表主鍵被 UPDATE/DELETE | 子表外鍵列置為 NULL | ✅ 支持 (需外鍵列允許 NULL) | 保留子記錄但斷開關聯，如員工離職部門保留 |
| SET DEFAULT | 父表主鍵被 UPDATE/DELETE | 子表外鍵列置為 DEFAULT 值 | ✅ 支持 (需定義 DEFAULT) | 歸檔歸屬未知歸檔部門 |
| CASCADE | 父表主鍵被 DELETE | 子表關聯行同步刪除 | ✅ 僅支持 DELETE | 訂單-明細、部門-員工等強生命週期綁定 |
| CASCADE (UPDATE) | 父表主鍵被 UPDATE | 子表外鍵值同步更新 | ❌ **不支持** | 需觸發器/應用層實現 |
CREATE TABLE child_table (
child_id NUMBER PRIMARY KEY,
parent_id NUMBER,
data_col VARCHAR2(100),
CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent_table(parent_id)
ON DELETE SET NULL -- 或 CASCADE / SET DEFAULT
-- ON UPDATE CASCADE -- Oracle 報錯：ORA-02256
);
Oracle **不支持** `ALTER TABLE ... MODIFY CONSTRAINT` 直接變更外鍵動作。標準流程：
-- 1. 查詢約束名
SELECT constraint_name, table_name, delete_rule
FROM user_constraints
WHERE constraint_type = 'R' AND table_name = 'CHILD_TABLE';
-- 2. 刪除舊約束
ALTER TABLE child_table DROP CONSTRAINT fk_child_parent;
-- 3. 重建新約束 (含所需動作)
ALTER TABLE child_table ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent_table(parent_id)
ON DELETE CASCADE; -- 新動作
-- 為外鍵列建立索引 (命名規範: IDX_表名_FK列名)
CREATE INDEX idx_emp_dept_id ON employees(dept_id);
CREATE INDEX idx_item_order_id ON order_items(order_id);
CREATE INDEX idx_ship_item_id ON shipments(item_id);
-- 父表增加狀態列
ALTER TABLE departments ADD (status VARCHAR2(1) DEFAULT 'A' CHECK (status IN ('A','D')));
-- 視圖過濾有效數據
CREATE VIEW v_active_departments AS
SELECT * FROM departments WHERE status = 'A';
-- 應用層邏輯刪除：UPDATE departments SET status='D' WHERE dept_id=:id;
-- 子表保持不變，報表/審計仍可關聯歷史部門
-- 1. 建立歸檔表 (結構同源表)
CREATE TABLE orders_archive PARTITION BY RANGE (order_date) (
PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01')
) AS SELECT * FROM orders WHERE 1=0;
-- 2. 批量歸檔 (避免巨大事務)
DECLARE
CURSOR c_old IS SELECT order_id FROM orders WHERE order_date < DATE '2024-01-01';
TYPE id_arr IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
l_ids id_arr;
BEGIN
OPEN c_old;
LOOP
FETCH c_old BULK COLLECT INTO l_ids LIMIT 10000;
EXIT WHEN l_ids.COUNT = 0;
FORALL i IN 1..l_ids.COUNT
DELETE FROM orders WHERE order_id = l_ids(i);
COMMIT; -- 分批提交
END LOOP;
CLOSE c_old;
END;
/
-- 3. 歸檔數據可通過 Partition Exchange Loading (PEL) 秒級移入歸檔表
CREATE OR REPLACE TRIGGER trg_dept_pk_cascade
AFTER UPDATE OF dept_id ON departments
FOR EACH ROW
BEGIN
IF :OLD.dept_id != :NEW.dept_id THEN
UPDATE employees SET dept_id = :NEW.dept_id WHERE dept_id = :OLD.dept_id;
-- 可擴展至多層子表
END IF;
END;
/
-- 注意：觸發器增加寫入延遲，需評估併發影響
