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

前一阵子写了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中产生一个不存在的人的治疗方案！

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

级联删除

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

语法：

Foreign Key

(column[,...n])

references referenced_table_name[(ref_column[,...n])]

[on delete cascade]

[on update cascade]

注释：

column:列名

referenced_table_name:外键参考的主键表名称

ref_name:外键要参考的表的主键列

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'

外键的作用

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

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

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

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

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

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

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

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

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

看看表中的数据

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

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

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

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

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

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

现在表结构为

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

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

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