關於 oracle 外來鍵引用 與 goldengate
一、準備知識
約束放置在表中，有以下五種約束:
NOT NULL 非空約束C 指定的列不允許為空值
UNIQUE 唯一約束U 指定的列中沒有重複值，或該表中每一個值或者每一組值都將是唯一的
PRIMARY KEY 主鍵約束P 唯一的標識出表的每一行,且不允許空值值,一個表只能有一個主鍵約束
FOREIGN KEY 外來鍵約束R 一個表中的列引用了其它表中的列，使得存在依賴關係，可以指向引用自身的列
CHECK 條件約束C 指定該列是否滿足某個條件
約束命名規則
如果不指定約束名Oracle server 自動按照SYS_Cn 的格式指定約束名，也可手動指定，
推薦的約束命名是：約束型別_表名_列名。
NN：NOT NULL 非空約束，比如nn_emp_sal
UK：UNIQUE KEY 唯一約束
PK：PRIMARY KEY 主鍵約束
FK：FOREIGN KEY 外來鍵約束
CK：CHECK 條件約束
外來鍵約束是用來維護從表和主表的引用完整性的，所以外來鍵約束要涉及兩個表。
FOREIGN KEY: 在表級指定子表中的列
REFERENCES: 標示在父表中的列
ON DELETE CASCADE: 當父表中的列被刪除時，子表中相對應的列也被刪除
ON DELETE SET NULL: 子表中相應的列置空
二、外來鍵建立測試
foreign_main為主表
foreign_sub為從表
object_id做為foreign_sub的外來鍵，參考主表foreign_main的object_id值
SQL> create table foreign_main as select object_id from all_objects;
Table created.
SQL> select count(*) from foreign_main;
COUNT(*)
----------
49571
SQL> create table foreign_sub as select object_id,object_name from all_objects;
Table created.
建議使用主表的主鍵做外來鍵，即使不是主表的主鍵也應該是唯一約束的欄位做為外來鍵
SQL> alter table foreign_main add constraint pk_fsid primary key(object_id);
Table altered.
SQL> delete from foreign_sub where object_name = 'FOREIGN_MAIN';
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter table foreign_sub add constraint fr_fssid foreign key(object_id) references foreign_main(object_id);
Table altered.
從表插入一條主表object_id中不存在的記錄測試
SQL> insert into foreign_sub values(1,'ts');
insert into foreign_sub values(1,'ts')
*
ERROR at line 1:
ORA-02291: integrity constraint (TEST.FR_FSSID) violated - parent key not found
提示主表資料不存在，從表不能建立主表不存在的object_id以保證完整性
三、級聯刪除測試
SQL> alter table foreign_sub drop constraint fk_fs_oid;
Table altered.
SQL> alter table foreign_sub add constraint fk_fs_oid foreign key(object_id) references foreign_main(object_id) on delete cascade;
Table altered.
cascade下仍然不能單獨更新主表外來鍵欄位
SQL> update foreign_main set object_id=52012 where object_id=52010;
update foreign_main set object_id=52012 where object_id=52010
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.FK_FS_OID) violated - child record found
cascade模式下可以透過主表刪除外來鍵欄位資料關聯刪除從表資料
SQL> select * from foreign_sub where object_id=52010;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
52010 IDX_BJNAME
SQL> delete from foreign_main where object_id=52010;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from foreign_sub where object_id=52010;
no rows selected
外來鍵相關常用操作及參考文件
建立外來鍵
alter table 表名 add constraint 外來鍵名 foreign key(從表外來鍵欄位) references foreign_main(主表外來鍵欄位);
drop表外來鍵
alter table 表名 drop constraint 外來鍵名;
透過外來鍵找表
select * from user_constraints where constraint_type='R' and constraint_name=upper('外來鍵名');
透過表找外來鍵
select * from user_constraints where constraint_type='R' and table_name=upper('表名');
查詢表的外來鍵（包括名稱，引用表的表名和對應的鍵名，下面是分成多步查詢）：
select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查詢的表
查詢引用表的鍵的列名：
select * from user_cons_columns cl where cl.constraint_name = 外來鍵引用表的鍵名
外來鍵約束臨時disabled
alter table 表名 disable constraint 外來鍵名;
在SQL92標準中定義了幾種外來鍵改變後，如何處理子表記錄的動作，其中包括：
限制Restrict：這種方式不允許對被參考的記錄的鍵值執行更新或刪除的操作；置為空Set to null：當參考的資料被更新或者刪除，那麼所有參考它的外來鍵值被置為空；
置為預設值Set to default：當參考的資料被更新或者刪除，那麼所有參考它的外來鍵值被置為一個預設值；
級聯Cascade：當參考的資料被更新，則參考它的值同樣被更新，當參考的資料被刪除，則參考它的子表記錄也被刪除；
不做操作No action：這種方式不允許更新或刪除被參考的資料。和限制方式的區別在於，這種方式的檢查發生在語句執行之後。Oracle預設才會的方式就是這種方式。
Col OWNER FOR A6
COL R_OWNER FOR A6
COL TABLE_NAME FOR A15
select OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE from user_constraints where table_name in ('FOREIGN_MAIN', 'FOREIGN_SUB');
Select CONSTRAINT_NAME from user_constraints e where e.table_name='IMS_COLUMN' and owner='WSJD_ELMS6';
Select b.table_name,b.column_name, A.CONSTRAINT_TYPE, C.TABLE_NAME from user_constraints a, user_cons_columns b, user_constraints C
WHERE a.constraint_name = b.constraint_name AND
A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND a.r_constraint_name IN (Select CONSTRAINT_NAME from user_constraints e where e.table_name='FOREIGN_MAIN' and owner='SCOTT');
create table foreign_sub as select object_id, object_name from user_objects;
create table foreign_main as select object_id from foreign_sub;
alter table foreign_main add constraint pk_foreign_main_object_id primary key(object_id);
alter table foreign_sub add constraint fr_foreign_sub_object_id foreign key(object_id) references foreign_main(object_id) on delete cascade;
alter table foreign_sub drop constraint fr_foreign_sub_object_id;
alter table foreign_sub disable constraint fr_foreign_sub_object_id;
如在goldengate 沒有禁用外來鍵約束會出現以現錯誤
=============================================
2013-12-26 04:51:25 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rep_app.prm: REPLICAT REP_APP started.
2013-12-26 04:51:25 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep_app.prm: 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 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Aborted grouped transaction on 'SCOTT.FOREIGN_MAIN', Database error 2292 (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 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
2013-12-26 04:51:25 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep_app.prm: SQL error 2292 mapping SCOTT.FOREIGN_MAIN to SCOTT.FOREIGN_MAIN 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 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
2013-12-26 04:51:25 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Error mapping from SCOTT.FOREIGN_MAIN to SCOTT.FOREIGN_MAIN.
2013-12-26 04:51:25 WARNING OGG-01525 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Failed to open trace output file, 'gglog-REP_APP.dmp', error 13 (Permission denied).
2013-12-26 04:51:25 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep_app.prm: PROCESS ABENDING.
2013-12-26 04:52:20 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rep_app.prm: REPLICAT REP_APP started.
2013-12-26 04:52:20 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep_app.prm: No unique key is defined for table 'FOREIGN_SUB'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2013-12-26 04:52:20 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Aborted grouped transaction on 'SCOTT.FOREIGN_SUB', Database error 1403 (OCI Error ORA-01403: no data found, SQL).
2013-12-26 04:52:20 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
2013-12-26 04:52:20 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep_app.prm: SQL error 1403 mapping SCOTT.FOREIGN_SUB to SCOTT.FOREIGN_SUB OCI Error ORA-01403: no data found, SQL.
2013-12-26 04:52:20 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
2013-12-26 04:52:20 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Error mapping from SCOTT.FOREIGN_SUB to SCOTT.FOREIGN_SUB.
2013-12-26 04:52:20 WARNING OGG-01525 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Failed to open trace output file, 'gglog-REP_APP.dmp', error 13 (Permission denied).
2013-12-26 04:52:20 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep_app.prm: PROCESS ABENDING.
約束放置在表中，有以下五種約束:
NOT NULL 非空約束C 指定的列不允許為空值
UNIQUE 唯一約束U 指定的列中沒有重複值，或該表中每一個值或者每一組值都將是唯一的
PRIMARY KEY 主鍵約束P 唯一的標識出表的每一行,且不允許空值值,一個表只能有一個主鍵約束
FOREIGN KEY 外來鍵約束R 一個表中的列引用了其它表中的列，使得存在依賴關係，可以指向引用自身的列
CHECK 條件約束C 指定該列是否滿足某個條件
約束命名規則
如果不指定約束名Oracle server 自動按照SYS_Cn 的格式指定約束名，也可手動指定，
推薦的約束命名是：約束型別_表名_列名。
NN：NOT NULL 非空約束，比如nn_emp_sal
UK：UNIQUE KEY 唯一約束
PK：PRIMARY KEY 主鍵約束
FK：FOREIGN KEY 外來鍵約束
CK：CHECK 條件約束
外來鍵約束是用來維護從表和主表的引用完整性的，所以外來鍵約束要涉及兩個表。
FOREIGN KEY: 在表級指定子表中的列
REFERENCES: 標示在父表中的列
ON DELETE CASCADE: 當父表中的列被刪除時，子表中相對應的列也被刪除
ON DELETE SET NULL: 子表中相應的列置空
二、外來鍵建立測試
foreign_main為主表
foreign_sub為從表
object_id做為foreign_sub的外來鍵，參考主表foreign_main的object_id值
SQL> create table foreign_main as select object_id from all_objects;
Table created.
SQL> select count(*) from foreign_main;
COUNT(*)
----------
49571
SQL> create table foreign_sub as select object_id,object_name from all_objects;
Table created.
建議使用主表的主鍵做外來鍵，即使不是主表的主鍵也應該是唯一約束的欄位做為外來鍵
SQL> alter table foreign_main add constraint pk_fsid primary key(object_id);
Table altered.
SQL> delete from foreign_sub where object_name = 'FOREIGN_MAIN';
1 row deleted.
SQL> commit;
Commit complete.
SQL> alter table foreign_sub add constraint fr_fssid foreign key(object_id) references foreign_main(object_id);
Table altered.
從表插入一條主表object_id中不存在的記錄測試
SQL> insert into foreign_sub values(1,'ts');
insert into foreign_sub values(1,'ts')
*
ERROR at line 1:
ORA-02291: integrity constraint (TEST.FR_FSSID) violated - parent key not found
提示主表資料不存在，從表不能建立主表不存在的object_id以保證完整性
三、級聯刪除測試
SQL> alter table foreign_sub drop constraint fk_fs_oid;
Table altered.
SQL> alter table foreign_sub add constraint fk_fs_oid foreign key(object_id) references foreign_main(object_id) on delete cascade;
Table altered.
cascade下仍然不能單獨更新主表外來鍵欄位
SQL> update foreign_main set object_id=52012 where object_id=52010;
update foreign_main set object_id=52012 where object_id=52010
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.FK_FS_OID) violated - child record found
cascade模式下可以透過主表刪除外來鍵欄位資料關聯刪除從表資料
SQL> select * from foreign_sub where object_id=52010;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
52010 IDX_BJNAME
SQL> delete from foreign_main where object_id=52010;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from foreign_sub where object_id=52010;
no rows selected
外來鍵相關常用操作及參考文件
建立外來鍵
alter table 表名 add constraint 外來鍵名 foreign key(從表外來鍵欄位) references foreign_main(主表外來鍵欄位);
drop表外來鍵
alter table 表名 drop constraint 外來鍵名;
透過外來鍵找表
select * from user_constraints where constraint_type='R' and constraint_name=upper('外來鍵名');
透過表找外來鍵
select * from user_constraints where constraint_type='R' and table_name=upper('表名');
查詢表的外來鍵（包括名稱，引用表的表名和對應的鍵名，下面是分成多步查詢）：
select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查詢的表
查詢引用表的鍵的列名：
select * from user_cons_columns cl where cl.constraint_name = 外來鍵引用表的鍵名
外來鍵約束臨時disabled
alter table 表名 disable constraint 外來鍵名;
在SQL92標準中定義了幾種外來鍵改變後，如何處理子表記錄的動作，其中包括：
限制Restrict：這種方式不允許對被參考的記錄的鍵值執行更新或刪除的操作；置為空Set to null：當參考的資料被更新或者刪除，那麼所有參考它的外來鍵值被置為空；
置為預設值Set to default：當參考的資料被更新或者刪除，那麼所有參考它的外來鍵值被置為一個預設值；
級聯Cascade：當參考的資料被更新，則參考它的值同樣被更新，當參考的資料被刪除，則參考它的子表記錄也被刪除；
不做操作No action：這種方式不允許更新或刪除被參考的資料。和限制方式的區別在於，這種方式的檢查發生在語句執行之後。Oracle預設才會的方式就是這種方式。
Col OWNER FOR A6
COL R_OWNER FOR A6
COL TABLE_NAME FOR A15
select OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE from user_constraints where table_name in ('FOREIGN_MAIN', 'FOREIGN_SUB');
Select CONSTRAINT_NAME from user_constraints e where e.table_name='IMS_COLUMN' and owner='WSJD_ELMS6';
Select b.table_name,b.column_name, A.CONSTRAINT_TYPE, C.TABLE_NAME from user_constraints a, user_cons_columns b, user_constraints C
WHERE a.constraint_name = b.constraint_name AND
A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND a.r_constraint_name IN (Select CONSTRAINT_NAME from user_constraints e where e.table_name='FOREIGN_MAIN' and owner='SCOTT');
create table foreign_sub as select object_id, object_name from user_objects;
create table foreign_main as select object_id from foreign_sub;
alter table foreign_main add constraint pk_foreign_main_object_id primary key(object_id);
alter table foreign_sub add constraint fr_foreign_sub_object_id foreign key(object_id) references foreign_main(object_id) on delete cascade;
alter table foreign_sub drop constraint fr_foreign_sub_object_id;
alter table foreign_sub disable constraint fr_foreign_sub_object_id;
如在goldengate 沒有禁用外來鍵約束會出現以現錯誤
=============================================
2013-12-26 04:51:25 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rep_app.prm: REPLICAT REP_APP started.
2013-12-26 04:51:25 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep_app.prm: 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 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Aborted grouped transaction on 'SCOTT.FOREIGN_MAIN', Database error 2292 (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 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
2013-12-26 04:51:25 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep_app.prm: SQL error 2292 mapping SCOTT.FOREIGN_MAIN to SCOTT.FOREIGN_MAIN 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 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
2013-12-26 04:51:25 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Error mapping from SCOTT.FOREIGN_MAIN to SCOTT.FOREIGN_MAIN.
2013-12-26 04:51:25 WARNING OGG-01525 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Failed to open trace output file, 'gglog-REP_APP.dmp', error 13 (Permission denied).
2013-12-26 04:51:25 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep_app.prm: PROCESS ABENDING.
2013-12-26 04:52:20 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rep_app.prm: REPLICAT REP_APP started.
2013-12-26 04:52:20 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep_app.prm: No unique key is defined for table 'FOREIGN_SUB'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2013-12-26 04:52:20 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Aborted grouped transaction on 'SCOTT.FOREIGN_SUB', Database error 1403 (OCI Error ORA-01403: no data found, SQL
2013-12-26 04:52:20 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
2013-12-26 04:52:20 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep_app.prm: SQL error 1403 mapping SCOTT.FOREIGN_SUB to SCOTT.FOREIGN_SUB OCI Error ORA-01403: no data found, SQL
2013-12-26 04:52:20 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Repositioning to rba 17426 in seqno 29.
2013-12-26 04:52:20 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Error mapping from SCOTT.FOREIGN_SUB to SCOTT.FOREIGN_SUB.
2013-12-26 04:52:20 WARNING OGG-01525 Oracle GoldenGate Delivery for Oracle, rep_app.prm: Failed to open trace output file, 'gglog-REP_APP.dmp', error 13 (Permission denied).
2013-12-26 04:52:20 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep_app.prm: PROCESS ABENDING.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/196700/viewspace-1163235/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle查詢表的外來鍵引用關係Oracle
- 父表修改與外來鍵的關係（主鍵DML與外來鍵的關係）
- indexedDB 內鍵與外來鍵Index
- 關於外來鍵的理解和實驗步驟
- Oracle 外來鍵查詢sqlOracleSQL
- Oracle根據主鍵查詢外來鍵Oracle
- 實現 MongoDB 外來鍵關聯MongoDB
- 關於外來鍵約束和對應主鍵資訊的查詢指令碼指令碼
- Django（15）外來鍵和表關係Django
- oracle外來鍵約束的總結Oracle
- Oracle 外來鍵的級聯處理Oracle
- Goldengate資料傳輸外來鍵問題OGG-00869Go
- 解決無法刪除表，提示被外來鍵約束引用
- 關於Oracle GoldenGate中Extract的checkpoint的理解OracleGo
- sqlserver外來鍵SQLServer
- Oracle定義約束 外來鍵約束Oracle
- Oracle 外來鍵索引影響阻塞問題Oracle索引
- Oracle系統中表外來鍵的更名(轉)Oracle
- 關於Oracle GoldenGate 引數TRANLOGOPTIONS altarchivelogdestOracleGoHive
- Mysql truncate table時解決外來鍵關聯MySql
- mysql啟動和關閉外來鍵約束MySql
- Oracle 查詢表與表之間的 主外來鍵關係Oracle
- oracle查詢表資訊（索引，外來鍵，列等）Oracle索引
- ORACLE 禁用/啟用外來鍵和觸發器Oracle觸發器
- Oracle '批量'禁用外來鍵的儲存過程Oracle儲存過程
- 小議Oracle外來鍵約束脩改行為（七）Oracle
- 小議Oracle外來鍵約束脩改行為（六）Oracle
- 小議Oracle外來鍵約束脩改行為（五）Oracle
- 小議Oracle外來鍵約束脩改行為（四）Oracle
- 小議Oracle外來鍵約束脩改行為（三）Oracle
- 小議Oracle外來鍵約束脩改行為（二）Oracle
- 小議Oracle外來鍵約束脩改行為（一）Oracle
- Oracle外來鍵約束中NULL的處理OracleNull
- MYSQL的外來鍵MySql
- 外來鍵技術
- 關於網友的獲取MSSQL外來鍵資訊的問題的探討SQL
- oracle 主外來鍵關係及實驗Oracle
- 關於引用物件拷貝物件