小议Oracle外键约束修改行为（一）

Oracle的外键用来限制子表中参考的字段的值 必须在主表中存在 而且在主表的记录发生变化导致外键参考唯一约束值发生了变化时 定义了一系列的动作

创新互联是一家集网站建设,建湖企业网站建设,建湖品牌网站建设,网站定制,建湖网站建设报价,网络营销,网络优化,建湖网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。

在SQL 标准中定义了几种外键改变后 如何处理子表记录的动作 其中包括

限制Restrict 这种方式不允许对被参考的记录的键值执行更新或删除的操作 置为空Set to null 当参考的数据被更新或者删除 那么所有参考它的外键值被置为空

置为默认值Set to default 当参考的数据被更新或者删除 那么所有参考它的外键值被置为一个默认值

级联Cascade 当参考的数据被更新 则参考它的值同样被更新 当参考的数据被删除 则参考它的子表记录也被删除

不做操作No action 这种方式不允许更新或删除被参考的数据 和限制方式的区别在于 这种方式的检查发生在语句执行之后 Oracle默认才会的方式就是这种方式

Oracle明确支持的方式包括No action Set to null和Cascade 对于Set to Default和Restrict Oracle的约束类型并不直接支持 不过可以通过触发器来实现

简单看一下Oracle的默认处理方式No action

SQL CREATE TABLE T_P (ID NUMBER NAME VARCHAR ( ));

表已创建

SQL ALTER TABLE T_P ADD PRIMARY KEY (ID);

表已更改

SQL CREATE TABLE T_C (ID NUMBER FID NUMBER NAME VARCHAR ( ));

表已创建

SQL ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);

表已更改

SQL INSERT INTO T_P VALUES ( A );

已创建 行

SQL INSERT INTO T_P VALUES ( B );

已创建 行

SQL INSERT INTO T_C VALUES ( A );

已创建 行

SQL MIT;

提交完成

对于No Action操作而言 如果主键的记录被外键所参考 那么主键记录是无法更新或删除的

SQL DELETE T_P WHERE ID = ;DELETE T_P WHERE ID = *第 行出现错误:ORA : 违反完整约束条件 (YANGTK FK_T_C) 已找到子记录日志

SQL UPDATE T_P SET ID = WHERE ID = ;UPDATE T_P SET ID = WHERE ID = *第 行出现错误:ORA : 违反完整约束条件 (YANGTK FK_T_C) 已找到子记录日志

SQL DELETE T_P WHERE ID = ;

已删除 行

不过No Action又和Restrict操作有所区别 No Action允许用户执行语句 在语句执行之后 或者事务结束的时候才会检查是否违反约束 而Restrict只有检测到有外键参考主表的记录 就不允许删除和更新的操作执行了

这也使得No Action操作支持延迟约束

SQL ALTER TABLE T_C DROP CONSTRAINT FK_T_C;

表已更改

SQL ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID) DEFERRABLE INITIALLY DEFERRED;

表已更改

SQL SELECT * FROM T_P;

ID NAME A

SQL SELECT * FROM T_C;

ID FID NAME A

SQL DELETE T_P WHERE ID = ;

已删除 行

SQL INSERT INTO T_P VALUES ( A );

已创建 行

SQL MIT;

提交完成

lishixinzhi/Article/program/Oracle/201311/17487

oracle 外键如何更新

用scott用户打开两个窗口

1、外键无索引时，子表更新外键未提交，主表更新非子表引用的主键时被阻塞

会话1：

create table t1 (x int primary key);

insert into t1 values(1);

insert into t1 values(2);

insert into t1 values(3);

commit;

create table t2（y int references t1）；

insert into t2 values（1）；

commit;

update t2 set y=2 where y=1;

会话2：

update t1 set x=4 where x=3; //会话被阻塞

2、外键有索引时，子表更新外键未提交，主表更新非子表引用的主键时不会被阻塞

会话1：

create index t2_index on t2(y) ; //创建外键索引

update t2 set y=2 where y=1;

会话2：

update t1 set x=4 where x=3;

已更新 1 行；//可以正常更新

3、外键有无索引，对于子表更新外键未提交，主表更新相对应的主键无影响，更新主键的session都会被阻塞

会话1：

update t2 set y=2 where y=1;

会话2：

update t1 set x=4 where x=1; //更新子表已引用的

会话被阻塞。

会话1：

update t2 set y=2 where y=1;

会话2：

update t1 set x=4 where x=2 ; //更新子表将要引用的

会话被阻塞。――很好理解，主表要判断是否违反约束

二、更新子表非外键列未提交

1、外键无索引，更新主表已被外键引用的主键时，更新主键的session被阻塞

会话1：

create table t1 (x int primary key,x1 int);

insert into t1 values(1,1);

insert into t1 values(2,2);

insert into t1 values(3,3);

commit ;

create table t2(y int references t1,y1 int);

insert into t2 values(1,1);

commit ;

update t2 set y1=2 where y1=1;

会话2：

update t1 set x=4 where x=1; //更新外键引用的主键

会话被阻塞。

2、外键有索引，更新主表已被外键引用的主键时，更新主键的session不会被阻塞而报约束错误

会话1：

create index t2_index on t2(y);

update t2 set y1=2 where y1=1;

会话2：

update t1 set x=4 where x=1

*

ERROR 位于第 1 行:

ORA-02292: 违反完整约束条件 (SCOTT.SYS_C001607) - 已找到子记录日志

3、外键无索引，更新主表未被外键引用的主键时，更新主键的session被阻塞

会话1：

drop index t2_index；

update t2 set y1=2 where y1=1

会话2:

update t1 set x=4 where x=2;

会话被阻塞。

4、外键有索引，更新主表未被外键引用的主键时，更新主键的session不会被阻塞

会话1：

create index t2_index on t2(y);

update t2 set y1=2 where y1=1;

会话2：

update t1 set x=4 where x=2;

已更新 1 行。

另外在一个主表有on delete cascade，子表没有外键索引时，对主表操作会级联到子表，子表将进行全表扫描。

总结：在需要更新主键的情况下，最好是创建子表的外键索引。

Oracle外键与其主键的实际应用方案

以下的文章主要是对Oracle主键与Oracle外键的实际应用方案的介绍 此篇文章是我很然偶在一网站上发现的 如果你对Oracle主键与Oracle外键的实际应用很感兴趣的话 以下的文章就会给你提供更详细的相关方面的知识

CREATE TABLE SCOTT MID_A_TAB

( A VARCHAR ( BYTE)

B VARCHAR ( BYTE)

DETPNO VARCHAR ( BYTE)

)TABLESPACE USERS ;

CREATE TABLE SCOTT MID_B_TAB

( A VARCHAR ( BYTE)

B VARCHAR ( BYTE)

DEPTNO VARCHAR ( BYTE)

)TABLESPACE USERS ;

给MID_A_TAB表添加主键

alter table mid_a_tab add constraint a_pk primary key (detpno);

给MID_B_TAB表添加Oracle主键

alter table mid_b_tab add constraint b_pk primary key(a);

给子表MID_B_TAB添加Oracle外键 并且引用主表MID_A_TAB的DETPNO列 并通过on delete cascade指定引用行为是级联删除

alter table mid_b_tab add constraint b_fk foreign key

(deptno) references mid_a_tab (detpno) on delete cascade;

向这样就创建了好子表和Oracle主表

向主表添加数据记录

SQL insert into mid_a_tab(a b detpno) values( );

已创建 行

已用时间: : :

向子表添加数据

SQL insert into mid_b_tab(a b deptno) values( );

insert into mid_b_tab values( )

*

第 行出现错误:

ORA : 违反唯一约束条件 (SCOTT B_PK)

已用时间: : :

可见上面的异常信息 那时因为子表插入的deptno的值是 然而此时我们主表中

detpno列只有一条记录那就是 所以当子表插入数据时 在父表中不能够找到该引用

列的记录 所以出现异常

但我们可以这样对子表的数据的进行插入(即 在子表的deptno列插入null 因为我们在建表的时候

并没有对该列进行not null的约束限制)

SQL insert into mid_b_tab(a b deptno) values( null);

已创建 行

已用时间: : :

现在如果我们把子表mid_b_tab中deptno列加上not null约束

SQL alter table mid_b_tab modify deptno not null;

alter table mid_b_tab modify deptno not null

*

第 行出现错误:

ORA : 无法启用 (SCOTT ) 找到空值

已用时间: : :

上面又出现异常 这是因为现在mid_b_tab表中有了一条记录 就是我们先前添加的

那条记录

null

现在我们要把该表的deptno列进行not null约束限制 所以Oracle不让我们这样干

那我们就只有把该表给delete或truncate掉 然后在修改deptno列为非空

SQL delete from mid_b_tab;

已删除 行

已用时间: : :

再次修改子表mid_b_tab表的deptno列为非空

SQL alter table mid_b_tab modify deptno not null;

表已更改

已用时间: : :

修改成功！

我们再次插入数据

insert into mid_b_tab(a b deptno) values( null);

试试

SQL insert into mid_b_tab(a b deptno) values( null);

insert into mid_b_tab(a b deptno) values( null)

*

第 行出现错误:

ORA : 无法将 NULL 插入 ( SCOTT MID_B_TAB DEPTNO )

已用时间: : :

看见现在Oracle不让我们插入空值了

所以我们在创建子表的Oracle外键约束时 该表的引用列必须要进行not null限制 也可以在

该列创建unique 或primary key约束 并且引用列与被引用列的数据类型必须相同

SQL insert into mid_b_tab(a b deptno) values( );

已创建 行

已用时间: : :

此时数据插入成功 因为此时插入的 在主表中的被引用列中已经存在了

现在我们一系列的操作

SQL select * from mid_b_tab ;

A B DE

已用时间: : :

SQL select * from mid_a_tab;

A B DE

已用时间: : :

SQL delete from mid_a_tab;

已删除 行

lishixinzhi/Article/program/Oracle/201311/18331

oracle中 怎么设主外键?

以oracle自带的用户scott为例。

create table dept(

deptno number(2) primary key, --deptno 为 dept表的主键

dname varchar2(10),

loc varchar2(9)

);

create table emp(

empno number(4) primary key, --empno 为 emp表的主键

ename varchar2(10),

job varchar2(9),

mgr number(4),

hiredate date,

sal number(7,2),

comm number(7,2),

deptno number(2) references dept(deptno) --dept表中deptno字段 为 emp表的外键

);