[20160716]主外键与延迟约束.txt

[20160716]主外键与延迟约束.txt

--前几天遇到的问题,因为开发没有在2个存在主外键上的表上建立约束,导致主表记录删除了,而外表数据还在.
--主要开发有需求要删除主表的记录,由于条件写错,导致以上情况出现.实际上oracle支持延迟约束,只有提交的时候才会检查。
--自己通过例子说明:

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING           VERSION    BANNER                                                                           CON_ID
--------------------- ---------- -------------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0  12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production          0

2.建立测试环境:

create table p ( x int primary key );
create table c ( x int );
insert into p values ( 1 );
insert into p values ( 2 );
insert into c values ( 1 );
commit;
create index i_c_x on scott.c(X);

3.如果建立约束:
SCOTT@test01p> alter table c add ( constraint fk_c foreign key (x) references p (x) enable validate);
Table altered.

SCOTT@test01p> delete from p where x=1;
delete from p where x=1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_C) violated - child record found
--这样在外键存在记录的情况下删除主键记录会报错. 而执行如下ok.

SCOTT@test01p> delete from p where x=2;
1 row deleted.

SCOTT@test01p> rollback ;
Rollback complete.

4.要实现提交时再判断,可以修改如下.
SCOTT@test01p> alter table c drop  constraint fk_c ;
Table altered.

SCOTT@test01p> alter table c add ( constraint fk_c foreign key (x) references p (x) deferrable initially deferred enable validate);
Table altered.

SCOTT@test01p> delete from p where x=1;
1 row deleted.

SCOTT@test01p> commit ;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (SCOTT.FK_C) violated - child record found

--这样在提交时才报错,而不是在执行时.

SCOTT@test01p> delete from p where x=1;
1 row deleted.

SCOTT@test01p> insert into p values ( 1 );
1 row created.

SCOTT@test01p> commit ;
Commit complete.

--这样不报错.

5.也可以这样执行:

SCOTT@test01p> insert into c values ( 3 );
1 row created.

SCOTT@test01p> insert into p values (3);
1 row created.

SCOTT@test01p> commit ;
Commit complete.

--不知道这样设置是否存在什么问题,对于我完全目前是不得以而为之.
--我们现在的问题主要在于开发不合理的开发模式,本来应该是update的问题,他们采用的做法就是先删除原来数据,再插入.
--开发太不了解oracle了.

时间: 2024-10-23 20:08:06

[20160716]主外键与延迟约束.txt的相关文章

[20160719]主外键与延迟约束2.txt

[20160719]主外键与延迟约束2.txt --前几天遇到的问题,因为开发没有在2个存在主外键上的表上建立约束,导致主表记录删除了,而外表数据还在. --主要开发有需求要删除主表的记录,由于条件写错,导致以上情况出现.实际上oracle支持延迟约束,只有提交的时候才会检查. --自己通过例子说明: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ---------------

[20151231]主外键与空表.txt

[20151231]主外键与空表.txt --主外键的测试例子很多,今天做一个特别的,外部键表为空,也会出现阻塞吗?测试看看. 1.建立环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------

[20121020]主外键约束以及NULL问题.txt

[20121020]主外键约束以及NULL问题.txt 主外键约束可以一定程度保证数据完整性,但是如果外键输入的是NULL,情况会如何呢? SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 6

[20160704]NULL与主外键问题.txt

[20160704]NULL与主外键问题.txt --主外键的问题主要出现在阻塞等情况,有许多极端dba认为应该取消这个约束.当然从使用的角度出发有总比没有好.只是不要过度滥用. --我这里遇到一个问题,开发指出主外键"失效",我仔细检查发现建立的主表索引是唯一,而非主键,这样可以插入NULL,导致所谓的"失效". --我开始意味是因为oracle索引不记录NULL而导致的(主键仅仅一个字段).实际上我的测试还是有点意外: 1.环境: SCOTT@book>

[20160616]IOT与主外键.txt

[20160616]IOT与主外键.txt https://ilmarkerm.eu/blog/2016/06/interesting-difference-in-foreign-key-locking-behavior-between-heap-and-index-organized-tables --许多人都知道主外键引起的阻塞或者死锁,如果主表是IOT呢? IOT实际上一种特殊的索引结构,测试看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING    

[20140210]主外键和阻塞.txt

[20140210]主外键和阻塞.txt 许多人都知道如果几个表之间存在主外键关系的情况下,许多情况下会出现阻塞情况. 具体的例子还很多,当然如果我觉得最常见如果你不修改主外键值,外键的索引多数情况下可以不建. 而且有些外键的索引建立有点多余的. 今天我看了一篇blog,链接如下: http://blog.yavor.info/?p=564&lang=en 给出的例子很奇特,就是这个问题在11g下会出现阻塞,而10g下不会.12c下我也做了测试,也不会, 自己重复测试做一个记录: 1.12c的情

[20151231]主外键与空表(12c).txt

[20151231]主外键与空表(12c).txt --主外键的测试例子很多,今天做一个特别的,外部键表为空,也会出现阻塞吗?测试看看. 1.建立环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID --------------

在PowerDesigner中设计物理模型1——表和主外键

原文:在PowerDesigner中设计物理模型1--表和主外键 在PD中建立物理模型由以下几种办法: 直接新建物理模型. 设计好概念模型,然后由概念模型生成物理模型. 设计好逻辑模型,然后由逻辑模型生成物理模型. 使用逆向工程的方法,连接到现有的数据库,由数据库生成物理模型. 物理模型能够直观的反应出当前数据库的结构.在数据库中的表.视图.存储过程等数据库对象都可以在物理模型中进行设计.由于物理模型和数据库的一致性,接下来以数据库对象和物理模型对象的对应来一一介绍: 表 新建物理模型时需要指定

主外键关联删除(on delete set null和on delete cascade)

主外键关联,当删除的是父表数据,参照这些要删除的数据,Oracle有三种处理方式: 1.禁止删除,也是Oracle默认方法. 2.将参照要删除数据的子表对应数据置空. 3.将参照要删除数据的子表对应数据删除. 对于1,比较容易理解,不解释. 对于2,需要使用on delete set null建立外键约束.实验: create table dept_test(deptno number(10) not null, deptname varchar2(30) not null, constrain