Oracle数据库中外键的相关操作整理
racle使用外键来限制子表中参考的字段值，要求子表中的数据必须在主表中存在。当主表的记录发生变化时导致外键参考唯一约束值发生了变化时，Oracle指定了三种动作：默认值（类似于restrict）、deletecascade和deletesetnull。(
1.创建父表并初始化数据
SQL>createtablet_parent(parent_idintprimarykey,namevarchar2(10)); Tablecreated. SQL>insertintot_parentvalues(1,'record1'); 1rowcreated. SQL>insertintot_parentvalues(2,'record2'); 1rowcreated. SQL>insertintot_parentvalues(3,'record3'); 1rowcreated. SQL>commit; Commitcomplete.
2.创建三种类型的子表t_child1、t_child2和t_child3
(1)noaction类别
SQL>createtablet_child1(child1_idintprimarykey,parent_idint); Tablecreated. SQL>altertablet_child1addconstraintFK_t_child1foreignkey(parent_id)referencest_parent(parent_id); Tablealtered. SQL>insertintot_child1values(1,1); 1rowcreated. SQL>commit; Commitcomplete.
(2)cascade类型
SQL>createtablet_child2(child2_idintprimarykey,parent_idint); Tablecreated. SQL>altertablet_child2addconstraintFK_t_child2foreignkey(parent_id)referencest_parent(parent_id)ondeletecascade; Tablealtered. SQL>insertintot_child2values(2,2); 1rowcreated. SQL>commit; Commitcomplete.
(3)SETNULL类型
SQL>createtablet_child3(child2_idintprimarykey,parent_idint); Tablecreated. SQL>altertablet_child3addconstraintFK_t_child3foreignkey(parent_id)referencest_parent(parent_id)ondeletesetnull; Tablealtered. SQL>insertintot_child3values(3,3); 1rowcreated. SQL>commit; Commitcomplete.
3.确认主表和子表中的数据
SQL>select*fromT_PARENT; PARENT_IDNAME -------------------- 1record1 2record2 3record3 SQL>select*fromT_CHILD1; CHILD1_IDPARENT_ID -------------------- 11 SQL>select*fromT_CHILD2; CHILD2_IDPARENT_ID -------------------- 22 SQL>select*fromT_CHILD3; CHILD2_IDPARENT_ID -------------------- 33
4.尝试对具有默认类型外键参照的主表记录进行删除
SQL>deletefromT_PARENTwhereparent_id=1; deletefromT_PARENTwhereparent_id=1 * ERRORatline1: ORA-02292:integrityconstraint(HBHE.FK_T_CHILD1)violated-childrecord found SQL>select*fromT_CHILD1; CHILD1_IDPARENT_ID -------------------- 11
在此类型下，不允许删除操作
5.尝试对具有deletecascade类型外键参照的主表记录进行删除
SQL>deletefromT_PARENTwhereparent_id=2; 1rowdeleted. SQL>select*fromT_CHILD2; norowsselected
级联删除成功
6.尝试对具有deletesetnull类型外键参照的主表记录进行删除
SQL>deletefromT_PARENTwhereparent_id=3; 1rowdeleted. SQL>select*fromT_CHILD3; CHILD2_IDPARENT_ID -------------------- 3
主表记录可以完成删除，子表中对应的内容被设置为NULL。
热门推荐
10 医院探望朋友祝福语简短
11 毕业祝福语简短女朋友
12 春节虎年爸妈祝福语简短
13 儿童毕业祝福语 简短6
14 开工仪式横幅祝福语简短
15 孩子日常祝福语大全简短
16 清晨寄语诗句祝福语简短
17 结婚送亲认亲祝福语简短
18 虎年喝酒拜年祝福语简短