在数据库中使用外键和级联删除

电玩女神 2021-09-30 16:58 489阅读 0赞

前一阵子写了1个项目，这个项目有ios和android2个版本，都使用了sqlite数据库。数据库内容也不是太复杂，但是我们在操作时没有利用数据库的级联删除等功能，导致代码复杂，现在分析一下。

比如系统需要2个表，表Person表示人的信息， 包含personID,personName,  表Treatement表示治疗方案，包含用药名称，用药剂量，用药时间。一个人可以对应多个治疗方案。

以下是我用的设计

CREATE TABLE "person" ("id" INT , "name" VARCHAR(20))
    
    CREATE TABLE "treatement" ("id" INTEGER, "personId" INTEGER , "time" TEXT)

看看这里的问题，

第一，没有使用主键，导致我在添加person时，需要验证是否已经存在相同的id。

第二，没有在第二张表中使用外键。第二张表中项目，表示一个人对应的治疗方案，一个人可以有多条治疗方案。如果这个人被删除了，那么所有的治疗方案也该被删除。这里perosnId应该设置为外键，关联表1的id，这样就可以利用级联删除，保证2张表的一致性。而且，还可以保证不会在表2中产生一个不存在的人的治疗方案！

**下面从网上摘抄一点关于外键和数据库的知识，感谢原作者的辛勤付出！**

--------------------

**级联删除 **

删除包含[主键][Link 1]值的行的操作，该值由其它表的现有行中的[外键][Link 2]列引用。在级联删除中，还删除其外键值引用删除的[主键][Link 1]值的所有行。

语法：

Foreign Key

(column\[,...n\])

references referenced\_table\_name\[(ref\_column\[,...n\])\]

\[on delete cascade\]

\[on update cascade\]

注释：

column:列名

referenced\_table\_name:[外键][Link 2]参考的[主键][Link 1]表名称

ref\_name:[外键][Link 2]要参考的表的[主键][Link 1]列

on delete:删除级联

on update:更新级联

SQL级联删除——删除主表同时删除从表——同时删除具有主外键关系的表

create table a

(

id varchar(20) primary key,

password varchar(20) not null

)

create table b

(

id int identity(1,1) primary key,

name varchar(50) not null,

userId varchar(20),

foreign key (userId) references a(id) on delete cascade

)

表B创建了外码userId 对应A的主码ID，声明了级联删除

测试数据：

insert a values ('11','aaa')

insert a values('23','aaa')

insert b values('da','11')

insert b values('das','11')

insert b values('ww','23')

删除A表内id为‘11’的数据，发现B表内userId 为“11”也被数据库自动删除了，这就是级联删除

delete a where id='11'

--------------------

** 外键的作用**

保持[数据一致性][Link 3]，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值或使用空值。

--------------------

如果仅仅声明了外键，而没有做级联操作，那么有以下结论：

1. 外键约束对insert语句的影响：

插入数据的外键字段值必须在主表中存在，只有从表才有可能违反约束，主表不会。

2.外键约束对delete语句的影响：

删除主表数据时，如果从表有对该数据的引用，主表才有可能违反约束。

3.外键约束对update语句的影响：

主从表都有可能违反外键约束，操作一个表必须将另一个表的数据处理好。

比如我有以下2个表，这里person表就是主表，treatement表就是从表，从表参照主表的键形成从表的外键。

![aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwMzM0NjI1Mzk5MDkucG5n][]

![aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwMzQwOTM0NTY1OTkucG5n][]

看看表中的数据

![aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwNDEzMDc5MTg4OTcucG5n][]

![aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwNDIyOTI4MzQxOTkucG5n][]

之后我试着插入一条treatement数据，这条数据的personID是10，在person表中不存在，如图

![aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwMzc1MDA5MTMxMDcucG5n][]

当我点击save时，系统报错，如图

![aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwMzg1NTU0MzQ2NjgucG5n][]

上边的例子就是外键约束对insert的影响。

在来看看外键约束对delete的影响，

如果我想删除person中的id = 1的条目，系统就会报错，因为在treatement表中存在外键引用，并且引用的值是 1，错误截图如下

![aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwNDYyMzc0OTgzMzAucG5n][]

**如果添加上级联删除，操作结果如下**

现在表结构为

CREATE TABLE "treatement" ("id" INTEGER PRIMARY KEY, "personId" INTEGER REFERENCES "person" ("id") ON DELETE CASCADE, "time" TEXT)

这时我就可以删除person表中id = 1 的项目了，删除后treatement中personId = 1 的项目同时被删除了。

注意，这里的级联删除需要在从表声明的，说明了自己被系统级联删除的参照条件。

[Link 1]: http://baike.baidu.com/view/68068.htm
[Link 2]: http://baike.baidu.com/view/68073.htm
[Link 3]: http://baike.baidu.com/view/4138648.htm
[aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwMzM0NjI1Mzk5MDkucG5n]: /images/20210812/f6e7759b5866403097a6368978e0bff7.png
[aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwMzQwOTM0NTY1OTkucG5n]: /images/20210812/d2c3db8bec2f4ebb9b6a89bb8c986755.png
[aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwNDEzMDc5MTg4OTcucG5n]: /images/20210812/2c988f5fbe5d48d3ba1cf33c52e6b353.png
[aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwNDIyOTI4MzQxOTkucG5n]: /images/20210812/d1718cb8c2374faeb73642d083ba4f28.png
[aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwMzc1MDA5MTMxMDcucG5n]: /images/20210812/f69de7f1b9a24ac4afdc5824964fca44.png
[aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwMzg1NTU0MzQ2NjgucG5n]: /images/20210812/541af8fe5aaa4a608ec83ebd2045d826.png
[aHR0cHM6Ly9pbWFnZXMwLmNuYmxvZ3MuY29tL2Jsb2cvNDkzNTQyLzIwMTQwMi8yMTEwNDYyMzc0OTgzMzAucG5n]: /images/20210812/db28d18b41394ceaa4168b5ac589f08a.png