萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> oracle教程 >> 關於oracle外鍵引用與goldengate

關於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 =

copyright © 萬盛學電腦網 all rights reserved