关于oracle外键引用与goldengate_Oracle教程-查字典教程网
关于oracle外键引用与goldengate
关于oracle外键引用与goldengate
发布时间:2015-06-05 来源:查字典编辑
摘要:一、准备知识约束放置在表中,有以下五种约束:NOTNULL非空约束C指定的列不允许为空值UNIQUE唯一约束U指定的列中没有重复值,或该表中...

一、准备知识

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

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.

相关阅读
推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
  • 大家都在看
  • 小编推荐
  • 猜你喜欢
  • 最新Oracle教程学习
    热门Oracle教程学习
    编程开发子分类