萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 外鍵約束與 GoldenGate 同步錯誤完整指南

一、前置知識:Oracle 約束類型總覽

在 Oracle 資料庫中,約束用於維護資料完整性,主要分為五類:

  • NOT NULL(非空約束,代碼 C):指定欄位不允許為空值。
  • UNIQUE(唯一約束,代碼 U):確保欄位或欄位組合的值唯一。
  • PRIMARY KEY(主鍵約束,代碼 P):唯一標識表中每一行,不允許空值,每張表僅能有一個主鍵。
  • FOREIGN KEY(外鍵約束,代碼 R):建立子表欄位對父表欄位的引用關係,支援自引用。
  • CHECK(條件約束,代碼 C):限定欄位值必須滿足特定條件。

約束命名規範

若未指定名稱,Oracle 會自動生成 SYS_Cn 格式名稱。建議採用「約束類型_表名_欄位名」格式,例如:

  • NN:非空約束 → nn_emp_sal
  • UK:唯一約束
  • PK:主鍵約束
  • FK:外鍵約束
  • CK:檢查約束

二、外鍵建立與測試實戰

外鍵用於維護父表與子表的參考完整性,必須涉及兩張表:

  • FOREIGN KEY:在子表層級指定參考欄位
  • REFERENCES:標示父表被參考欄位
  • ON DELETE CASCADE:父表刪除時,子表關聯資料同步刪除
  • ON DELETE SET NULL:父表刪除時,子表對應欄位設為 NULL

實驗環境準備

-- 建立父表
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;

-- 新增外鍵約束
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

錯誤證明:子表無法插入父表不存在的 object_id,確保參考完整性。

三、級聯刪除測試

-- 重建帶 CASCADE 的外鍵
ALTER TABLE foreign_sub DROP CONSTRAINT fk_fs_oid;
ALTER TABLE foreign_sub ADD CONSTRAINT fk_fs_oid 
  FOREIGN KEY (object_id) REFERENCES foreign_main (object_id) ON DELETE CASCADE;

-- 嘗試更新父表主鍵(仍會失敗)
UPDATE foreign_main SET object_id = 52012 WHERE object_id = 52010;
-- ORA-02292: integrity constraint violated - child record found

-- 刪除父表記錄,子表自動級聯刪除
DELETE FROM foreign_main WHERE object_id = 52010;
COMMIT;
-- 子表對應記錄已同步刪除

四、外鍵常用管理語句

操作語法範例
建立外鍵ALTER TABLE 表名 ADD CONSTRAINT 外鍵名 FOREIGN KEY(子表欄位) REFERENCES 父表(父表欄位);
刪除外鍵ALTER TABLE 表名 DROP CONSTRAINT 外鍵名;
查詢外鍵詳情SELECT * FROM user_constraints WHERE constraint_type = 'R' AND table_name = UPPER('表名');
查詢被引用欄位SELECT * FROM user_cons_columns WHERE constraint_name = '外鍵引用的約束名';
暫停/啟用外鍵ALTER TABLE 表名 DISABLE/ENABLE CONSTRAINT 外鍵名;

五、SQL92 標準定義的參考動作

  • RESTRICT(限制):不允許更新/刪除被參考記錄。
  • SET NULL(置空):參考資料變更時,外鍵值設為 NULL。
  • SET DEFAULT(置默認值):參考資料變更時,外鍵值設為預設值。
  • CASCADE(級聯):參考資料更新/刪除時,子表同步更新/刪除。
  • NO ACTION(無動作):不允許更新/刪除被參考資料,檢查發生在語句執行後(Oracle 預設行為)。

六、GoldenGate 同步外鍵衝突案例

在 GoldenGate 複製環境中,若目標端未禁用外鍵約束,執行父表刪除操作時會拋出 ORA-02292 錯誤,導致 Replicat 進程中斷:

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
DELETE FROM "SCOTT"."FOREIGN_MAIN" WHERE "OBJECT_ID" = :b0

解決方案

  1. 在 Replicat 參數檔中加入 HANDLECOLLISIONS 或使用 SQLEXEC 暫停約束。
  2. 或於目標端執行:ALTER TABLE 子表 DISABLE CONSTRAINT 外鍵名; 後再啟動同步,同步完成後重新啟用。
  3. 生產環境建議:採用 ON DELETE CASCADE 或應用層控制刪除順序,避免複製端約束衝突。

七、總結

外鍵是關聯式資料庫維護資料一致性的核心機制。開發階段應明確定義約束類型與刪除規則;部署 GoldenGate 等複寫工具時,務必評估約束對同步效能與正確性的影響,採取適當的約束管理策略,確保異構環境下的資料完整性。

copyright © 萬盛學電腦網 all rights reserved