oracle如何修改外鍵 oracle修改外鍵約束

小議Oracle外鍵約束修改行為（一）

Oracle的外鍵用來(lái)限制子表中參考的字段的值 必須在主表中存在 而且在主表的記錄發(fā)生變化導(dǎo)致外鍵參考唯一約束值發(fā)生了變化時(shí) 定義了一系列的動(dòng)作

創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),建湖企業(yè)網(wǎng)站建設(shè),建湖品牌網(wǎng)站建設(shè),網(wǎng)站定制,建湖網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷,網(wǎng)絡(luò)優(yōu)化,建湖網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

在SQL 標(biāo)準(zhǔn)中定義了幾種外鍵改變后 如何處理子表記錄的動(dòng)作 其中包括

限制Restrict 這種方式不允許對(duì)被參考的記錄的鍵值執(zhí)行更新或刪除的操作 置為空Set to null 當(dāng)參考的數(shù)據(jù)被更新或者刪除 那么所有參考它的外鍵值被置為空

置為默認(rèn)值Set to default 當(dāng)參考的數(shù)據(jù)被更新或者刪除 那么所有參考它的外鍵值被置為一個(gè)默認(rèn)值

級(jí)聯(lián)Cascade 當(dāng)參考的數(shù)據(jù)被更新 則參考它的值同樣被更新 當(dāng)參考的數(shù)據(jù)被刪除 則參考它的子表記錄也被刪除

不做操作No action 這種方式不允許更新或刪除被參考的數(shù)據(jù) 和限制方式的區(qū)別在于 這種方式的檢查發(fā)生在語(yǔ)句執(zhí)行之后 Oracle默認(rèn)才會(huì)的方式就是這種方式

Oracle明確支持的方式包括No action Set to null和Cascade 對(duì)于Set to Default和Restrict Oracle的約束類型并不直接支持 不過(guò)可以通過(guò)觸發(fā)器來(lái)實(shí)現(xiàn)

簡(jiǎn)單看一下Oracle的默認(rèn)處理方式No action

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

表已創(chuàng)建

SQL ALTER TABLE T_P ADD PRIMARY KEY (ID);

表已更改

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

表已創(chuàng)建

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

表已更改

SQL INSERT INTO T_P VALUES ( A );

已創(chuàng)建 行

SQL INSERT INTO T_P VALUES ( B );

已創(chuàng)建 行

SQL INSERT INTO T_C VALUES ( A );

已創(chuàng)建 行

SQL MIT;

提交完成

對(duì)于No Action操作而言 如果主鍵的記錄被外鍵所參考 那么主鍵記錄是無(wú)法更新或刪除的

SQL DELETE T_P WHERE ID = ;DELETE T_P WHERE ID = *第 行出現(xiàn)錯(cuò)誤:ORA : 違反完整約束條件 (YANGTK FK_T_C) 已找到子記錄日志

SQL UPDATE T_P SET ID = WHERE ID = ;UPDATE T_P SET ID = WHERE ID = *第 行出現(xiàn)錯(cuò)誤:ORA : 違反完整約束條件 (YANGTK FK_T_C) 已找到子記錄日志

SQL DELETE T_P WHERE ID = ;

已刪除 行

不過(guò)No Action又和Restrict操作有所區(qū)別 No Action允許用戶執(zhí)行語(yǔ)句 在語(yǔ)句執(zhí)行之后 或者事務(wù)結(jié)束的時(shí)候才會(huì)檢查是否違反約束 而Restrict只有檢測(cè)到有外鍵參考主表的記錄 就不允許刪除和更新的操作執(zhí)行了

這也使得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 );

已創(chuàng)建 行

SQL MIT;

提交完成

lishixinzhi/Article/program/Oracle/201311/17487

oracle 外鍵如何更新

用scott用戶打開(kāi)兩個(gè)窗口

1、外鍵無(wú)索引時(shí)，子表更新外鍵未提交，主表更新非子表引用的主鍵時(shí)被阻塞

會(huì)話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;

會(huì)話2：

update t1 set x=4 where x=3; //會(huì)話被阻塞

2、外鍵有索引時(shí)，子表更新外鍵未提交，主表更新非子表引用的主鍵時(shí)不會(huì)被阻塞

會(huì)話1：

create index t2_index on t2(y) ; //創(chuàng)建外鍵索引

update t2 set y=2 where y=1;

會(huì)話2：

update t1 set x=4 where x=3;

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

3、外鍵有無(wú)索引，對(duì)于子表更新外鍵未提交，主表更新相對(duì)應(yīng)的主鍵無(wú)影響，更新主鍵的session都會(huì)被阻塞

會(huì)話1：

update t2 set y=2 where y=1;

會(huì)話2：

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

會(huì)話被阻塞。

會(huì)話1：

update t2 set y=2 where y=1;

會(huì)話2：

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

會(huì)話被阻塞。――很好理解，主表要判斷是否違反約束

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

1、外鍵無(wú)索引，更新主表已被外鍵引用的主鍵時(shí)，更新主鍵的session被阻塞

會(huì)話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;

會(huì)話2：

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

會(huì)話被阻塞。

2、外鍵有索引，更新主表已被外鍵引用的主鍵時(shí)，更新主鍵的session不會(huì)被阻塞而報(bào)約束錯(cuò)誤

會(huì)話1：

create index t2_index on t2(y);

update t2 set y1=2 where y1=1;

會(huì)話2：

update t1 set x=4 where x=1

*

ERROR 位于第 1 行:

ORA-02292: 違反完整約束條件 (SCOTT.SYS_C001607) - 已找到子記錄日志

3、外鍵無(wú)索引，更新主表未被外鍵引用的主鍵時(shí)，更新主鍵的session被阻塞

會(huì)話1：

drop index t2_index；

update t2 set y1=2 where y1=1

會(huì)話2:

update t1 set x=4 where x=2;

會(huì)話被阻塞。

4、外鍵有索引，更新主表未被外鍵引用的主鍵時(shí)，更新主鍵的session不會(huì)被阻塞

會(huì)話1：

create index t2_index on t2(y);

update t2 set y1=2 where y1=1;

會(huì)話2：

update t1 set x=4 where x=2;

已更新 1 行。

另外在一個(gè)主表有on delete cascade，子表沒(méi)有外鍵索引時(shí)，對(duì)主表操作會(huì)級(jí)聯(lián)到子表，子表將進(jìn)行全表掃描。

總結(jié)：在需要更新主鍵的情況下，最好是創(chuàng)建子表的外鍵索引。

Oracle外鍵與其主鍵的實(shí)際應(yīng)用方案

以下的文章主要是對(duì)Oracle主鍵與Oracle外鍵的實(shí)際應(yīng)用方案的介紹 此篇文章是我很然偶在一網(wǎng)站上發(fā)現(xiàn)的 如果你對(duì)Oracle主鍵與Oracle外鍵的實(shí)際應(yīng)用很感興趣的話 以下的文章就會(huì)給你提供更詳細(xì)的相關(guān)方面的知識(shí)

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列 并通過(guò)on delete cascade指定引用行為是級(jí)聯(lián)刪除

alter table mid_b_tab add constraint b_fk foreign key

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

向這樣就創(chuàng)建了好子表和Oracle主表

向主表添加數(shù)據(jù)記錄

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

已創(chuàng)建 行

已用時(shí)間: : :

向子表添加數(shù)據(jù)

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

insert into mid_b_tab values( )

*

第 行出現(xiàn)錯(cuò)誤:

ORA : 違反唯一約束條件 (SCOTT B_PK)

已用時(shí)間: : :

可見(jiàn)上面的異常信息 那時(shí)因?yàn)樽颖聿迦氲膁eptno的值是 然而此時(shí)我們主表中

detpno列只有一條記錄那就是 所以當(dāng)子表插入數(shù)據(jù)時(shí) 在父表中不能夠找到該引用

列的記錄 所以出現(xiàn)異常

但我們可以這樣對(duì)子表的數(shù)據(jù)的進(jìn)行插入(即 在子表的deptno列插入null 因?yàn)槲覀冊(cè)诮ū淼臅r(shí)候

并沒(méi)有對(duì)該列進(jìn)行not null的約束限制)

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

已創(chuàng)建 行

已用時(shí)間: : :

現(xiàn)在如果我們把子表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

*

第 行出現(xiàn)錯(cuò)誤:

ORA : 無(wú)法啟用 (SCOTT ) 找到空值

已用時(shí)間: : :

上面又出現(xiàn)異常 這是因?yàn)楝F(xiàn)在mid_b_tab表中有了一條記錄 就是我們先前添加的

那條記錄

null

現(xiàn)在我們要把該表的deptno列進(jìn)行not null約束限制 所以O(shè)racle不讓我們這樣干

那我們就只有把該表給delete或truncate掉 然后在修改deptno列為非空

SQL delete from mid_b_tab;

已刪除 行

已用時(shí)間: : :

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

SQL alter table mid_b_tab modify deptno not null;

表已更改

已用時(shí)間: : :

修改成功！

我們?cè)俅尾迦霐?shù)據(jù)

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)

*

第 行出現(xiàn)錯(cuò)誤:

ORA : 無(wú)法將 NULL 插入 ( SCOTT MID_B_TAB DEPTNO )

已用時(shí)間: : :

看見(jiàn)現(xiàn)在Oracle不讓我們插入空值了

所以我們?cè)趧?chuàng)建子表的Oracle外鍵約束時(shí) 該表的引用列必須要進(jìn)行not null限制 也可以在

該列創(chuàng)建unique 或primary key約束 并且引用列與被引用列的數(shù)據(jù)類型必須相同

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

已創(chuàng)建 行

已用時(shí)間: : :

此時(shí)數(shù)據(jù)插入成功 因?yàn)榇藭r(shí)插入的 在主表中的被引用列中已經(jīng)存在了

現(xiàn)在我們一系列的操作

SQL select * from mid_b_tab ;

A B DE

已用時(shí)間: : :

SQL select * from mid_a_tab;

A B DE

已用時(shí)間: : :

SQL delete from mid_a_tab;

已刪除 行

lishixinzhi/Article/program/Oracle/201311/18331

oracle中 怎么設(shè)主外鍵?

以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表的外鍵

);

當(dāng)前文章：oracle如何修改外鍵 oracle修改外鍵約束
分享網(wǎng)址：http://muchs.cn/article14/hhedde.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App設(shè)計(jì)、網(wǎng)站收錄、網(wǎng)站營(yíng)銷、面包屑導(dǎo)航、網(wǎng)站設(shè)計(jì)公司、App開(kāi)發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)