Heim >Datenbank >MySQL-Tutorial >关于oracle外键引用与goldengate

关于oracle外键引用与goldengate

WBOY
WBOYOriginal
2016-06-07 16:21:001189Durchsuche

一、准备知识 约束放置在表中,有以下五种约束: NOT NULL 非空约束C 指定的列不允许为空值 UNIQUE 唯一约束U 指定的列中没有重复值,或该表中每一个值或者每一组值都将是唯一的 PRIMARY KEY 主键约束P 唯一的标识出表的每一行,且不允许空值值,一个表只能有一

   一、准备知识

  约束放置在表中,有以下五种约束:

  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.

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn