mysql之外键约束学习

学习目标

• 掌握外键约束的添加方法
• 知道外键约束字段的添加及删除规则
• 了解外键约束的优缺点

1.多表查询概述

l实际开发中，一个项目通常需要很多张表才能完成。

l例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。

一对多关系：

常见实例：客户和订单，分类和商品，部门和员工。

一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键。

2.外键约束

2.1外键约束的定义

现在我们有两张表“分类表”和“商品表”

为了表明商品属于哪个分类，通常情况下，==我们将在商品表上添加一列，用于存放分类cid的信息==，此列称为：外键

此时“分类表==category”称为：主表==，“cid”我们称为主键。“商品表==products”称为：从表==，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是==一对多关系==。

外键特点：

从表外键的值是对主表主键的引用。

从表外键类型，必须与主表主键类型一致。

举例：

上述表中，product表中的两条记录中的商品，都属于汽车分类。

category分类表，为唯一方，也就是主表，必须提供主键cid

products商品表，为多方，也就是从表，必须提供外键category_id

2.2绑定外键约束

为了我们可以更加清楚的体现商品表与分类表之间的联系，我们准备以下两张表，并建立外键关联关系：

绑定外键约束的格式如下：

在创表语句后添加：CONSTRAINT FOREIGN KEY (外键字段) REFERENCES 主表名(主键)

# 创建分类表
CREATE TABLE category
(
    cid   VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100) #分类名称
);

# 商品表
CREATE TABLE products
(
    pid         VARCHAR(32) PRIMARY KEY,
    name        VARCHAR(40),
    price       DOUBLE,
    category_id VARCHAR(32),
#     CONSTRAINT 约束
#     REFERENCES 参考
    CONSTRAINT FOREIGN KEY (category_id) REFERENCES category (cid) # 添加约束
);

# 查看表结构
# 主表不需要进行任何操作,从表在表内设置外键字段,绑定主表主键即可
DESC products;
DESC category;

此时，主表结构为：

从表结构为：

外键约束绑定成功。

2.3外键约束检测

插入数据

接下来，我们向分类（category）表中添加数据记录。

INSERT INTO category (cid ,cname) VALUES('c001','服装');

向商品表添加普通数据,没有外键数据，默认为null

INSERT INTO products (pid,pname) VALUES('p001','土豆');

然后向商品（product）表中添加数据记录，外键信息填写刚才插入的主表主键的值”c001“

INSERT INTO products (pid ,pname ,category_id) VALUES('p002','夹克','c001');

思考：如果在从表中插入的外键值,在主表中不存在会怎样? 我们来试一下：

INSERT INTO products VALUES ('p003', '坦克', 1200000, 'c002');

此时将会报错，错误信息为：Cannot add or update a child row: a foreign key constraint fails (test01.products, CONSTRAINTproducts_ibfk_1FOREIGN KEY (category_id) REFERENCEScategory(cid))。

得出结论：==当我们引用的外键,在主表主键中没有相同的值,则不能插入，但是可以插入空值==。

删除数据

我们将分类（category）表中的’c001‘数据进行删除

DELETE FROM category WHERE cid = 'c001';

此时将会报错，错误信息为Cannot delete or update a parent row: a foreign key constraint fails (test01.products, CONSTRAINTproducts_ibfk_1FOREIGN KEY (category_id) REFERENCEScategory(cid))。

为什么会出现这种情况呢？？

我们再尝试一下，先将商品（product）表中，引用主表中该条数据的记录删除。

DELETE FROM products WHERE pid = '002';

删除后，此时没有任何从表记录引用主表的c001分类，我们再次尝试删除分类（category）表中的’c001‘数据。

DELETE FROM category WHERE cid = 'c001';

删除成功。

由此我们得出结论：==如果要删除主表中的记录,需要先将从表中所有引用该数据的记录删除或者修改为引用其他记录==。

2.4外键约束的优点

• ==在插入数据时，保证了数据的准确性==
• ==在删除数据时，保证了数据的完整性==
• ==业务开发方面，代码量会相应减小==

2.5外键约束的缺点

• 可以用触发器或应用程序保证数据的完整性
• 过分强调或者说使用主键／外键会平添开发难度，导致表过多等问题
• 不用外键时数据管理简单，操作方便，性能高（导入导出等操作，在insert,update,delete数据的时候更快）
• 每次做数据的修改和删除的时候都必须要考虑外键约束，会导致数据的复杂性增加
• 会降低性能
• 级联删除问题：多级级联删除会让数据变的不可控，触发器也被严格禁用
• 数据耦合问题：数据库层面数据关系产生耦合，数据迁移维护困难
• 并发问题，外键约束会启用行级锁，主表写入时会进入阻塞

eg:在海量的数据库中想都不要去想外键，试想，一个程序每天要insert数百万条记录，当存在外键约束的时候，每次要去扫描此记录是否合格，一般还不止一个字段有外键，这样扫描的数量是成级数的增长！可能会造成系统的积压，可能还会造成系统的延迟和奔溃的情况
 

3.小结

• 实际开发中，一个项目通常需要很多张表才能完成。
• 一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键。
  • 从表外键的值是对主表主键的引用。
  • 从表外键类型，必须与主表主键类型一致。
• 从表中引用了主表中的数据，主表中数据不可被删除。
• 主表中没有数据，从表外键也无法被插入。
• 在大型系统中（性能要求不高，安全要求高），使用外键；在大型系统中（性能要求高，安全自己控制），不用外键；小系统随便，最好用外键。
• 用外键要适当，不能过分追求
• 不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后个个应用通过这个层来访问数据库