外键约束可以起到限定了一个列的取值范围的作用。通过这个实验，展示一下主外键参照关系中一些有趣的现象（约束与限制）。

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博客

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

最新文章