欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Oracle數(shù)據(jù)庫中外鍵的相關(guān)操作整理

 更新時間：2016年01月15日 17:41:00   作者：hbhe0316  
這篇文章主要介紹了Oracle數(shù)據(jù)庫中外鍵的相關(guān)操作整理,包括對外鍵參照的主表記錄進(jìn)行刪除的操作方法等,需要的朋友可以參考下

racle使用外鍵來限制子表中參考的字段值，要求子表中的數(shù)據(jù)必須在主表中存在。當(dāng)主表的記錄發(fā)生變化時導(dǎo)致外鍵參考唯一約束值發(fā)生了變化時，Oracle指定了三種動作：默認(rèn)值（類似于restrict）、delete cascade和delete set null。(
1.創(chuàng)建父表并初始化數(shù)據(jù)

SQL> create table t_parent (parent_id int primary key, name varchar2(10));
Table created.
SQL> insert into t_parent values (1,'record1');
1 row created.
SQL> insert into t_parent values (2,'record2');
1 row created.
SQL> insert into t_parent values (3,'record3');
1 row created.
SQL> commit;
Commit complete.

2.創(chuàng)建三種類型的子表t_child1、t_child2和t_child3
(1)no action類別

SQL> create table t_child1 (child1_id int primary key, parent_id int);
Table created.
SQL> alter table t_child1 add constraint FK_t_child1 foreign key (parent_id) references t_parent (parent_id);
Table altered.
SQL> insert into t_child1 values (1,1);
1 row created.
SQL> commit;
Commit complete.

(2)cascade類型

SQL> create table t_child2 (child2_id int primary key, parent_id int);
Table created.
SQL> alter table t_child2 add constraint FK_t_child2 foreign key (parent_id) references t_parent (parent_id) on delete cascade;
Table altered.
SQL> insert into t_child2 values (2,2);
1 row created.
SQL> commit;
Commit complete.

(3)SET NULL類型

SQL> create table t_child3 (child2_id int primary key, parent_id int);
Table created.
SQL> alter table t_child3 add constraint FK_t_child3 foreign key (parent_id) references t_parent (parent_id) on delete set null;
Table altered.
SQL> insert into t_child3 values (3,3);
1 row created.
SQL> commit;
Commit complete.

3.確認(rèn)主表和子表中的數(shù)據(jù)

SQL> select * from T_PARENT;
PARENT_ID NAME
---------- ----------
     1 record1
     2 record2
     3 record3
SQL> select * from T_CHILD1;
CHILD1_ID PARENT_ID
---------- ----------
     1     1
SQL> select * from T_CHILD2;
 CHILD2_ID PARENT_ID
---------- ----------
     2     2
SQL> select * from T_CHILD3;
 CHILD2_ID PARENT_ID
---------- ----------
     3     3



4.嘗試對具有默認(rèn)類型外鍵參照的主表記錄進(jìn)行刪除

SQL> delete from T_PARENT where parent_id = 1;
delete from T_PARENT where parent_id = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (HBHE.FK_T_CHILD1) violated - child record
found
SQL> select * from T_CHILD1;


 CHILD1_ID PARENT_ID
---------- ----------
     1     1

在此類型下，不允許刪除操作

5.嘗試對具有delete cascade類型外鍵參照的主表記錄進(jìn)行刪除

SQL> delete from T_PARENT where parent_id = 2;
1 row deleted.
SQL> select * from T_CHILD2;
no rows selected

級聯(lián)刪除成功


6.嘗試對具有delete set null類型外鍵參照的主表記錄進(jìn)行刪除

SQL> delete from T_PARENT where parent_id = 3;


1 row deleted.

SQL> select * from T_CHILD3;


 CHILD2_ID PARENT_ID
---------- ----------
     3

  主表記錄可以完成刪除，子表中對應(yīng)的內(nèi)容被設(shè)置為NULL。

相關(guān)文章

最新評論