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。