【重学 MySQL】六十六、外键约束的使用

【重学 MySQL】六十六、外键约束的使用

在MySQL中，外键约束是一种重要的数据库约束，用于确保表中的数据完整性。它强制子表中的每个记录都引用主表中的一个现有的记录，从而维护数据的一致性和完整性。

外键约束的概念

外键约束是作用于表中字段上的规则，用于限制存储在表中的数据。它使得两张表的数据之间能够建立连接，从而确保数据的一致性和完整性。

关键字

FOREIGN KEY

主表和从表/父表和子表

主表（父表）：被引用的表，被参考的表

从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。

例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

外键约束的创建条件

1. 主表存在：主表必须已经存在于数据库中，或者是当前正在创建的表。
2. 主键定义：必须为主表定义主键。
3. 主键非空：主键不能包含空值，但允许在外键中出现空值。
4. 列匹配：在主表的表名后面指定列名或列名的组合，这个列或列的组合必须是主表的主键。同时，外键中列的数目必须和主表的主键中列的数目相同。
5. 数据类型一致：外键中列的数据类型必须和主表主键列的数据类型相同。

外键约束的特点

1. 从表的外键列，必须引用/参考主表的主键或唯一约束的列 为什么？因为被依赖/被参考的值必须是唯一的
2. 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。
3. 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
4. 删表时，先删从表（或先删除外键约束），再删除主表
5. 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
6. 在“从表”中指定外键约束，并且一个表可以建立多个外键约束
7. 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t create table.database.tablename’(errno: 150)”。 例如：都是表示部门编号，都是int类型。
8. 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）
9. 删除外键约束后，必须手动删除对应的索引

外键约束的创建方式

1. 在创建表时设置外键约束

CREATE TABLE child_table (
    child_column INT NOT NULL,
    PRIMARY KEY (child_column),
    FOREIGN KEY (child_column) REFERENCES parent_table (parent_column)
);

在上述语法中，child_table是子表名称，parent_table是主表名称，child_column是子表中的外键列，parent_column是主表中的引用列。

1. 通过ALTER TABLE添加外键约束

如果表已经存在，并且需要添加外键约束，可以使用ALTER TABLE语句。例如：

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column);

其中，fk_name是外键约束的名称，child_column是子表中的外键列，parent_table是主表名称，parent_column是主表中的引用列。

外键约束的删除

如果不再需要外键约束，可以使用ALTER TABLE语句将其删除。例如：

ALTER TABLE child_table
DROP FOREIGN KEY fk_name;

其中，child_table是子表名称，fk_name是要删除的外键约束的名称。

外键约束的约束等级

在MySQL中，外键约束的约束等级决定了当主表中的记录被更新或删除时，子表中相应的外键记录将如何响应。

1. CASCADE：
   • 含义：当主表中的记录被删除或更新时，子表中所有引用该记录的外键记录也会被相应地删除或更新。
   • 示例：如果主表中的某个部门被删除，那么所有属于该部门的员工记录（在子表中）也会被自动删除。
2. SET NULL：
   • 含义：当主表中的记录被删除或更新时，子表中所有引用该记录的外键记录的外键字段会被设置为NULL。这要求子表的外键列不能为NOT NULL约束。
   • 示例：如果主表中的某个部门被删除，那么所有属于该部门的员工记录（在子表中）的部门ID字段会被设置为NULL。
3. NO ACTION 或 RESTRICT：
   • 含义：这两种约束等级在MySQL中的行为是相似的。它们都会阻止对主表中被引用的记录进行删除或更新操作，如果子表中存在引用该记录的外键记录。
   • 示例：如果尝试删除主表中某个被子表引用的部门记录，数据库将拒绝该删除操作，直到所有引用该部门的员工记录被删除或更新。
4. SET DEFAULT：
   • 含义：这个约束等级在MySQL的InnoDB存储引擎中是不被支持的。理论上，它意味着当主表中的记录被删除或更新时，子表中所有引用该记录的外键记录会被设置为一个默认值。
   • 注意：由于InnoDB不支持，因此在实际应用中不会使用此约束等级。

在实际应用中，选择哪种约束等级取决于具体的业务需求。例如，如果希望当主表中的记录被删除时，子表中相应的记录也被删除，那么可以选择CASCADE约束等级。如果希望保留子表中的记录，但将外键字段设置为NULL以表示不再引用主表中的记录，那么可以选择SET NULL约束等级。

需要注意的是，外键约束的创建和使用需要满足一定的条件，如主表必须存在、主键必须定义、数据类型必须一致等。此外，不同的数据库管理系统（DBMS）可能对外键约束的支持和实现方式有所不同，因此在使用时需要参考具体DBMS的文档和指南。

外键约束的级联操作

1. 级联删除：当主表中的记录被删除时，如果子表中有依赖于该记录的外键，则这些外键对应的记录也将被自动删除。这可以通过在创建外键约束时指定ON DELETE CASCADE选项来实现。
2. 级联更新：当主表中的记录被更新时，如果子表中有依赖于该记录的外键，并且希望这些外键对应的记录也相应更新，则可以使用ON UPDATE CASCADE选项。

外键约束的示例

假设有两个表：department（部门表）和employee（员工表）。我们希望确保每个员工都属于一个存在的部门，因此可以在employee表中为dept_id列添加外键约束，引用department表中的id列。

-- 创建部门表
CREATE TABLE department (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

-- 创建员工表并添加外键约束
CREATE TABLE employee (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    job VARCHAR(20),
    salary INT,
    dept_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (dept_id) REFERENCES department(id)
);

或者，如果员工表已经存在，可以使用ALTER TABLE语句添加外键约束：

ALTER TABLE employee
ADD CONSTRAINT fk_employee_department
FOREIGN KEY (dept_id)
REFERENCES department(id);

这样，当尝试在employee表中插入一个不存在的dept_id时，数据库将拒绝该操作，从而保证了数据的一致性和完整性。

外键约束的作用

1. 保证数据完整性：外键约束可防止在子表中插入指向不存在记录的外键值。
2. 强制数据关联：外键约束强制子表中的记录与主表中的记录相关联。
3. 简化数据维护：外键约束简化了数据的维护，因为当主表中的记录被删除或更新时，子表中的记录将自动更新或删除（如果启用了级联操作）。

综上所述，外键约束在MySQL中扮演着重要的角色，它有助于维护数据库中的数据完整性和一致性。在使用外键约束时，需要确保满足其创建条件，并正确地创建和删除外键约束。

开发场景

问题1：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否一定要建外键约束？

答：不是的

问题2：建和不建外键约束有什么区别？

答：建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。

不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的引用完整性，只能依靠程序员的自觉，或者是在Java程序中进行限定。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。

问题3：那么建和不建外键约束和查询有没有关系？

答：没有

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以， MySQL 允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

阿里开发规范

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。