在 Oracle 資料庫中,約束用於維護資料完整性,主要分為五類:
若未指定名稱,Oracle 會自動生成 SYS_Cn 格式名稱。建議採用「約束類型_表名_欄位名」格式,例如:
nn_emp_sal外鍵用於維護父表與子表的參考完整性,必須涉及兩張表:
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 外鍵名; |
在 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
HANDLECOLLISIONS 或使用 SQLEXEC 暫停約束。ALTER TABLE 子表 DISABLE CONSTRAINT 外鍵名; 後再啟動同步,同步完成後重新啟用。ON DELETE CASCADE 或應用層控制刪除順序,避免複製端約束衝突。外鍵是關聯式資料庫維護資料一致性的核心機制。開發階段應明確定義約束類型與刪除規則;部署 GoldenGate 等複寫工具時,務必評估約束對同步效能與正確性的影響,採取適當的約束管理策略,確保異構環境下的資料完整性。