時間：2015-06-28 00:00:00 來源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評論(0)
CREATE TABLE "SCOTT"."MID_A_TAB"
(??? "A" VARCHAR2(20 BYTE),
"B" VARCHAR2(10 BYTE),
"DETPNO" VARCHAR2(10 BYTE)
)TABLESPACE "USERS" ;
CREATE TABLE "SCOTT"."MID_B_TAB"
(??? "A" VARCHAR2(20 BYTE),
"B" VARCHAR2(10 BYTE),
"DEPTNO" VARCHAR2(10 BYTE)
)TABLESPACE "USERS" ;
--給MID_A_TAB表添加主鍵
alter table mid_a_tab add constraint a_pk primary key (detpno);
--給MID_B_TAB表添加主鍵
alter table mid_b_tab add constraint b_pk primary key(a);
--給子表MID_B_TAB添加外鍵,并且引用主表MID_A_TAB的DETPNO列，并通過on delete cascade指定引用行為是級聯(lián)刪除
alter table mid_b_tab add constraint b_fk foreign key (deptno) references mid_a_tab (detpno) on delete cascade;
--向這樣就創(chuàng)建了好子表和主表
向主表添加數(shù)據(jù)記錄
SQL> insert into mid_a_tab(a,b,detpno)? values('1','1','10');
已創(chuàng)建 1 行。
已用時間:? 00: 00: 00.00
向子表添加數(shù)據(jù)
SQL> insert into mid_b_tab(a,b,deptno) values('1','2','6');
insert into mid_b_tab values('1','2','6')
*
第 1 行出現(xiàn)錯誤:
ORA-00001: 違反唯一約束條件 (SCOTT.B_PK)
已用時間:? 00: 00: 00.00
可見上面的異常信息，那時因為子表插入的deptno的值是6，然而此時我們主表中
detpno列只有一條記錄那就是10，所以當子表插入數(shù)據(jù)時，在父表中不能夠找到該引用
列的記錄，所以出現(xiàn)異常。
但我們可以這樣對子表的數(shù)據(jù)的進行插入(即：在子表的deptno列插入null,因為我們在建表的時候
并沒有對該列進行not null的約束限制)：
SQL> insert into mid_b_tab(a,b,deptno) values('3','2',null);
已創(chuàng)建 1 行。
已用時間:? 00: 00: 00.00
現(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
*
第 1 行出現(xiàn)錯誤:
ORA-02296: 無法啟用 (SCOTT.) - 找到空值
已用時間:? 00: 00: 00.01
上面又出現(xiàn)異常，這是因為現(xiàn)在mid_b_tab表中有了一條記錄，就是我們先前添加的
那條記錄。
3，2，null
現(xiàn)在我們要把該表的deptno列進行not null約束限制，所以oracle不讓我們這樣干。
那我們就只有把該表給delete或truncate掉，然后在修改deptno列為非空。
SQL> delete from mid_b_tab;
已刪除2行。
已用時間:? 00: 00: 00.01
再次修改子表mid_b_tab表的deptno列為非空。
SQL> alter table mid_b_tab modify deptno not null;
表已更改。
已用時間:? 00: 00: 00.01
修改成功！
我們再次插入數(shù)據(jù)
insert into mid_b_tab(a,b,deptno) values('13','2',null);試試。
SQL> insert into mid_b_tab(a,b,deptno) values('13','2',null);
insert into mid_b_tab(a,b,deptno) values('13','2',null)
*
第 1 行出現(xiàn)錯誤:
ORA-01400: 無法將 NULL 插入 ("SCOTT"."MID_B_TAB"."DEPTNO")
已用時間:? 00: 00: 00.00
看見現(xiàn)在oracle不讓我們插入空值了。
所以我們在創(chuàng)建子表的外鍵約束時，該表的引用列必須要進行not null限制，也可以在
該列創(chuàng)建unique，或primary key約束，并且引用列與被引用列的數(shù)據(jù)類型必須相同。
SQL> insert into mid_b_tab(a,b,deptno) values('13','2','10');
已創(chuàng)建 1 行。
已用時間:? 00: 00: 00.01
此時數(shù)據(jù)插入成功，因為此時插入的10，在主表中的被引用列中已經(jīng)存在了。
現(xiàn)在我們一系列的操作：
SQL> select * from mid_b_tab ;
A? B??? DE
-------------------- ---------- --
13 2??? 10
已用時間:? 00: 00: 00.00
SQL> select * from mid_a_tab;
A? B??? DE
-------------------- ---------- --
1? 1??? 10
已用時間:? 00: 00: 00.00
SQL> delete from mid_a_tab;
已刪除 1 行。
已用時間:? 00: 00: 00.01
#p#副標題#e#
SQL> select * from mid_b_tab;
未選定行
從上邊的操作中可以看出當我們刪除了主表中的記錄后，子表中相應的記錄
也被級聯(lián)刪除掉了。
通過引用行為可以確定如何處理子表中的外鍵字段。引用類型包括3中類型：
1.on delete cascade;--級聯(lián)刪除
2.on set null;--刪除主表中的記錄后，子表中的相應記錄的列被設置為null(但子表的該字段必須支持null值)。
3.on no action;--不允許刪除主表中被引用的數(shù)據(jù)，該操作會被禁止。
如果有on delete cascade，而且沒有在子表上加索引：那么每刪除主表中的一行
都會對子表做一個全表掃描。而且如果從父表刪除多行，那么每從父表中刪除一行
就要掃描一次子表。
SQL> select * from mid_a_tab a,mid_b_tab b where
2? a.detpno=b.deptno;
A? B??? DETPNO???? A? B??? DEPTNO
-------------------- ---------- ---------- -------------------- ---------- ----------
1? 2??? 12?? 2? 2??? 12
1? 2??? 12?? 1? 1??? 12
2? 22?? 13?? 22 212? 13
3? 33?? 14?? 55 6666 14
已用時間:? 00: 00: 00.00
執(zhí)行計劃
-------------------------------
關鍵詞標簽：oracle主鍵和外鍵
相關閱讀
熱門文章
Oracle中使用alter table來增加,刪除,修改列的語法
oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例
誤刪Oracle數(shù)據(jù)庫實例的控制文件
為UNIX服務器設置Oracle全文檢索
人氣排行 oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例 Oracle中使用alter table來增加,刪除,修改列的語法 ORACLE SQL 判斷字符串是否為數(shù)字的語句 ORACLE和SQL語法區(qū)別歸納(1) oracle grant 授權(quán)語句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法