ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实验】【外键】小议外键约束对应用程序的影响

【实验】【外键】小议外键约束对应用程序的影响

原创 Linux操作系统 作者:secooler 时间:2009-08-28 23:26:06 0 删除 编辑
外键约束可以起到限定了一个列的取值范围的作用。通过这个实验,展示一下主外键参照关系中一些有趣的现象(约束与限制)。

1.创建父表t_parent,并初始化父表数据
1)创建父表,其中包含两列,第一列存放小名或绰号(nickname),第二列存放全名(fullname)信息
sec@ora10g> create table t_parent (nickname varchar2(10), fullname varchar2(30)) tablespace tbs_sec_d;

Table created.

2)在nickname列上创建主键
sec@ora10g> alter table t_parent add constraint pk_t_parent primary key (nickname) using index tablespace tbs_sec_i;

Table altered.

3)向父表中初始化如下4条数据
sec@ora10g> insert into t_parent values ('sec', 'Secooler Hou');
sec@ora10g> insert into t_parent values ('Hou', 'Andy Hou');
sec@ora10g> insert into t_parent values ('TuTu', 'Daining Hou');
sec@ora10g> insert into t_parent values ('Xu', 'Anna Xu');
sec@ora10g> insert into t_parent values ('RQ', 'Richard Qin');
sec@ora10g> commit;

2.创建子表t_child,并初始化子表数据
1)创建子表,这里简单的包含两列信息,一列是这个表的id信息t_child_id,另外一列为nickname,用作参照完整性引用列
sec@ora10g> create table t_child (t_child_id number not null, nickname varchar2(10)) tablespace tbs_sec_d;

Table created.

2)在t_child_id列建立主键
sec@ora10g> alter table t_child add constraint pk_t_child primary key (t_child_id) using index tablespace tbs_sec_i;

Table altered.

3)创建外键约束。创建完成后这个子表t_child的nickname列将会参照父表t_parent的nickname列内容
sec@ora10g> alter table t_child add constraint fk_t_child_nickname foreign key (nickname) references t_parent (nickname);

Table altered.

创建外键约束的语法里需要注意的一个细节
上面的创建语句完全可以写成如下的简略形式,如果没有显示的指定参照字段,默认参照字段是父表的主键列。
alter table t_child add constraint fk_t_child_nickname foreign key (nickname) references t_parent;
但是,如果外键字段参照的是非主键字段,则必须在add constraint语句中指定字段名。

4)在外键上创建索引。当应用程序投入生产之前,一定要记得给外键创建索引,否则会因为子表的锁定机制导致应用程序效率极其低下!
sec@ora10g> create index t_child_idx on t_child (nickname);

Index created.

5)初始化如下十条数据,展示一个父表和子表之间的一对多的关系
sec@ora10g> insert into t_child values (1, 'sec');
sec@ora10g> insert into t_child values (2, 'TuTu');
sec@ora10g> insert into t_child values (3, 'sec');
sec@ora10g> insert into t_child values (4, 'Hou');
sec@ora10g> insert into t_child values (5, '');
sec@ora10g> insert into t_child values (6, '');
sec@ora10g> insert into t_child values (7, 'Xu');
sec@ora10g> insert into t_child values (8, 'Xu');
sec@ora10g> insert into t_child values (9, 'Xu');
sec@ora10g> insert into t_child values (10, 'Xu');
sec@ora10g> commit;

3.查看一下初始化数据之后父子表中的数据情况,有一个直观的印象
sec@ora10g> select * from t_parent;

NICKNAME   FULLNAME
---------- ------------------------------
RQ         Richard Qin
sec        Secooler Hou
Hou        Andy Hou
TuTu       Daining Hou
Xu         Anna Xu

sec@ora10g> select * from t_child;

T_CHILD_ID NICKNAME
---------- ----------
         1 sec
         2 TuTu
         3 sec
         4 Hou
         5
         6
         7 Xu
         8 Xu
         9 Xu
        10 Xu

10 rows selected.

4.到此,已经完成了整个父子表的初始化工作。我们来探索一下在主外键参照关系中都有哪些有趣的现象(约束与限制)
1)有趣现象一:对父表更新的限制
sec@ora10g> update t_parent set nickname='NEW' where nickname='RQ';

1 row updated.

sec@ora10g> update t_parent set nickname='NEW' where nickname='sec';
update t_parent set nickname='NEW' where nickname='sec'
*
ERROR at line 1:
ORA-02292: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - child record found

为什么第一条可以成功,但是第二条无法完成呢?
原因很简单,因为“sec”已经在子表中存在了,所有不能对父表的对应记录进行更新操作;
然而,“RQ”因为没有在子表中出现过,所以没有这个限制。

2)有趣现象二:对父表删除的限制
sec@ora10g> delete from t_parent where nickname = 'RQ';

1 row deleted.

sec@ora10g> delete from t_parent where nickname = 'sec';
delete from t_parent where nickname = 'sec'
*
ERROR at line 1:
ORA-02292: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - child record found

为什么第一条可以成功,但是第二条无法完成呢?
原因也很简单,和第一个现象的原因相同。仅仅可以删除子表中没有使用过的父表记录,对子表中已经应用的信息父表是不能随便删除的。

不过,有一个被称作“级联删除”的选项可以改变上面的行为,演示如下:
先drop掉原有的外键约束
sec@ora10g> alter table t_child drop constraint fk_t_child_nickname;

Table altered.

再创建带级联删除(on delete cascade)功能的外键约束
sec@ora10g> alter table t_child add constraint fk_t_child_nickname foreign key (nickname) references t_parent (nickname) on delete cascade;

Table altered.

OK,重新演示一下上面的删除语句
为比较删除前和删除后的区别,先得到删除前的父子表中数据情况
sec@ora10g> select * from t_parent;

NICKNAME   FULLNAME
---------- ------------------------------
RQ         Richard Qin
sec        Secooler Hou
Hou        Andy Hou
TuTu       Daining Hou
Xu         Anna Xu

sec@ora10g> select * from t_child;

T_CHILD_ID NICKNAME
---------- ----------
         1 sec
         2 TuTu
         3 sec
         4 Hou
         5
         6
         7 Xu
         8 Xu
         9 Xu
        10 Xu

10 rows selected.

执行删除操作,可以看到,这回操作成功了。
sec@ora10g> delete from t_parent where nickname = 'sec';

1 row deleted.

看一下删除后的父子表中数据情况
sec@ora10g> select * from t_parent;

NICKNAME   FULLNAME
---------- ------------------------------
RQ         Richard Qin
Hou        Andy Hou
TuTu       Daining Hou
Xu         Anna Xu

sec@ora10g> select * from t_child;

T_CHILD_ID NICKNAME
---------- ----------
         2 TuTu
         4 Hou
         5
         6
         7 Xu
         8 Xu
         9 Xu
        10 Xu

8 rows selected.

发现效果了吧,不但父表中有关“sec”的记录被删除了,而且,子表中的所有与“sec”关联的信息(这个实验中是两条“sec”记录)也同样的被删除掉了。这就是级联删除选项在起作用。

3)有趣现象三:对子表插入的限制
sec@ora10g> insert into t_child values (11,'Rex');
insert into t_child values (11,'Rex')
*
ERROR at line 1:
ORA-02291: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - parent key not found

不能向子表中插入这条数据的原因非常的简单:因为主表中没有“Rex”记录,所以这条子表插入操作被拒之门外了。

4)有趣现象四:对子表更新的限制
sec@ora10g> update t_child set nickname = 'Rex' where nickname = 'sec';
update t_child set nickname = 'Rex' where nickname = 'sec'
*
ERROR at line 1:
ORA-02291: integrity constraint (SEC.FK_T_CHILD_NICKNAME) violated - parent key not found

不能更新子表的原因也是因为父表中不存在“Rex”信息,所以没有办法更新成这个值。从错误提示信息中可以得到有效的提醒。

5.外键约束小结
在数据库表设计的过程中,外键约束可以有效的保证表与表之间的约束关系。

永远记住在创建外键之后,要在外键上创建索引!

深刻了解外键带给我们的约束和限制场景,避免在应用使用中出现不该有的错误。
总结一下外键的约束限制:
1)对父表更新的限制:不能把父表中的值更新为子表仍在使用而父表中不存在的值;
2)对父表删除的限制:子表中引用的父表记录不允许被删除;
3)对子表插入的限制:不允许相子表中插入父表中不存在的值;
4)对子表更新的限制:不允许将子表的值更新为父表中不存在的值。

本人是“实践派”的,用实验说话,并一直坚持认为:基本概念是“万物之源”。希望这个小文儿能对您澄清外键约束这个概念有所帮助。

Goodluck.

-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-613521/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    7977764