Oracle 外鍵 (Foreign Key) 維護父子表參考完整性，但在 GoldenGate (OGG) 異構同步場景下，外鍵約束常導致 Replicat 進程報錯 ORA-02292 (子記錄存在) 或 ORA-01403 (無資料)。本文系統梳理外鍵類型、建立/刪除/暫停語法、級聯規則、OGG 衝突根因與解決方案。
| 類型 | 代碼 | 關鍵字 | 功能 |
|---|---|---|---|
| 非空 | C | NOT NULL | 欄位不允許 NULL |
| 唯一 | U | UNIQUE | 值唯一，允許 NULL |
| 主鍵 | P | PRIMARY KEY | 唯一 + NOT NULL，單表僅一個 |
| 外鍵 | R | FOREIGN KEY ... REFERENCES | 子表欄位引用父表主鍵/唯一鍵 |
| 檢查 | C | CHECK | 滿足指定條件 |
命名規範：FK_子表_父表_欄位 (如 FK_FOREIGN_SUB_FOREIGN_MAIN_OBJECT_ID)
-- 父表
CREATE TABLE foreign_main AS SELECT object_id FROM all_objects;
ALTER TABLE foreign_main ADD CONSTRAINT PK_FSID PRIMARY KEY(object_id);
-- 子表
CREATE TABLE foreign_sub AS SELECT object_id, object_name FROM all_objects;
DELETE FROM foreign_sub WHERE object_name = 'FOREIGN_MAIN'; COMMIT;
-- 建立外鍵 (預設 NO ACTION)
ALTER TABLE foreign_sub ADD CONSTRAINT FR_FSSID FOREIGN KEY(object_id) REFERENCES foreign_main(object_id);
-- 測試：插入父表不存在的值
INSERT INTO foreign_sub VALUES(1, 'ts');
-- ORA-02291: integrity constraint violated - parent key not found-- 重建外鍵啟用級聯
ALTER TABLE foreign_sub DROP CONSTRAINT FR_FSSID;
ALTER TABLE foreign_sub ADD CONSTRAINT FK_FS_OID FOREIGN KEY(object_id)
REFERENCES foreign_main(object_id) ON DELETE CASCADE;
-- 父表刪除 → 子表關聯行自動刪除
DELETE FROM foreign_main WHERE object_id=52010; COMMIT;
SELECT * FROM foreign_sub WHERE object_id=52010; -- no rows selected
-- 注意：CASCADE 僅支援刪除，不支援 UPDATE 父鍵值
UPDATE foreign_main SET object_id=52012 WHERE object_id=52010;
-- ORA-02292: integrity constraint violated - child record found| 動作 | 關鍵字 | 行為 | Oracle 支援 |
|---|---|---|---|
| 限制 | RESTRICT | 拒絕父表更新/刪除 | 隱含支援 (預設 NO ACTION) |
| 置空 | SET NULL | 子表外鍵欄位設 NULL | 支援 ON DELETE SET NULL |
| 置默認值 | SET DEFAULT | 子表設為默認值 | 不支援 |
| 級聯 | CASCADE | 子表同步刪除/更新 | 支援 ON DELETE CASCADE，不支援 UPDATE CASCADE |
| 無動作 | NO ACTION | 語句結束後檢查，違反則回滾 | 預設行為 |
-- 查表的外鍵
SELECT * FROM user_constraints WHERE constraint_type='R' AND table_name='TABLE_NAME';
-- 查外鍵引用的父表欄位
SELECT * FROM user_cons_columns WHERE constraint_name='FK_NAME';
-- 多表關聯查詢
SELECT a.constraint_name, a.table_name, a.constraint_type,
b.column_name, c.table_name AS ref_table
FROM user_constraints a
JOIN user_cons_columns b ON a.constraint_name = b.constraint_name
JOIN user_constraints c ON a.r_constraint_name = c.constraint_name
WHERE a.constraint_type = 'R' AND a.table_name = 'CHILD_TABLE';
-- 暫停/啟用外鍵
ALTER TABLE table_name DISABLE CONSTRAINT fk_name;
ALTER TABLE table_name ENABLE CONSTRAINT fk_name;2013-12-26 04:51:25 WARNING OGG-00869 OCI Error ORA-02292: integrity constraint (SCOTT.FR_FOREIGN_SUB_OBJECT_ID) violated - child record found (status = 2292). DELETE FROM "SCOTT"."FOREIGN_MAIN" WHERE "OBJECT_ID" = :b0.
2013-12-26 04:51:25 ERROR OGG-01668 PROCESS ABENDING.| 方案 | 適用場景 | 操作 | 風險 |
|---|---|---|---|
| 1. 目標端暫停外鍵 | 初始載入、大批量同步 | ALTER TABLE ... DISABLE CONSTRAINT ... 同步完成後 ENABLE | 同步期間完整性暫時失效 |
2. Replicat 參數 HANDLECOLLISIONS | 初始同步、主鍵衝突 | 自動轉換 INSERT/UPDATE/DELETE 處理重複/缺失 | 不處理外鍵約束錯誤 |
3. SQLEXEC 捕獲錯誤延遲重試 | 間歇性衝突 | Replicat 參數檔編寫錯誤處理邏輯 | 複雜度高 |
| 4. 源端啟用級聯刪除 | 業務允許級聯 | ON DELETE CASCADE，目標端同步建立 | 業務邏輯變更 |
| 5. 同步順序控制 | 多表同步 | 先同步子表刪除，再同步父表刪除 (OGG 依賴主鍵順序) | 需精心設計映射規則 |
DISABLE CONSTRAINT 所有外鍵，載入完成後 ENABLE NOVALIDATE 再 VALIDATEGROUPTRANSOPS 1000、HANDLECOLLISIONS (初始階段)、錯誤處理 REPERROR (DEFAULT, ABEND)GGSCI> INFO REP *, DETAIL 定期檢查延遲與錯誤外鍵是資料完整性的守門員，但在高吞吐異構同步中需靈活「開門」。掌握約束管理語法與 OGG 參數配合，讓完整性與可用性共存。
