MySQL 外键约束

外键约束（Foreign Key Constraint）是关系型数据库中用于维护数据完整性和确保表间数据一致性的一种重要机制。以下是对外键约束的详细解释和几个关键点：

定义

外键约束定义了两个表之间的关系，通常涉及一个“主表”（父表）和一个“从表”（子表）。外键列（位于从表中）引用主表中的主键列或唯一键列，这意味着从表中的每一项数据在主表中都必须有对应的项。这种关系确保了引用完整性，即不能在从表中插入不存在于主表中的值。

目的

维护引用完整性：确保从表中的外键值与主表中的主键值相匹配，避免无效数据。
建立关联关系：定义表间的关系，便于理解和操作相关数据。
防止数据不一致性：在更新或删除主表数据时，自动处理从表中的相关数据，以保持数据间的一致性。

创建外键约束

在创建外键约束时，需要指定以下几点：

从表的列：哪些列将作为外键。
引用的主表及列：外键列将引用哪个表的哪个列。
可选约束名称：为外键约束指定一个名称，以便于管理和引用。
约束行为：定义在更新或删除主表数据时，从表应如何响应（CASCADE、SET NULL、RESTRICT、NO ACTION等）。

示例

假设有一个 departments表（主表）和一个 employees表（从表），其中 departments表有 dept_id为主键，而 employees表有个 dept_id作为外键引用 departments表的 dept_id。

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
    -- 可以在此处添加ON DELETE 和 ON UPDATE 规则
);

更新和删除约束

当尝试更新或删除主表中的数据时，如果从表中有相关记录，根据外键约束的行为设定（如RESTRICT、CASCADE等），操作可能被禁止或自动调整从表数据。
删除或修改外键约束需要使用ALTER TABLE语句。

性能考量

外键约束会略微影响写入性能，因为数据库需要额外验证引用完整性。
对于大量数据操作，特别是涉及级联操作的场景，性能影响更加显著。

总结

外键约束是关系数据库设计中不可或缺的一部分，它确保了数据的一致性和完整性，同时也帮助定义了数据模型中的实体关系。虽然它们可能会对性能产生一定影响，但在多数情况下，数据的准确性和一致性所带来的好处远超其潜在的性能开销。正确使用外键约束是设计高质量数据库的关键。

在MySQL中，使用 ALTER TABLE命令来添加或删除外键约束是一种常见的操作。下面我将通过具体的例子来展示如何添加外键约束、如何使用 SHOW CREATE TABLE来确认约束是否成功添加，以及如何删除外键约束并再次使用 SHOW CREATE TABLE来确认删除操作是否生效。

添加外键约束

假设我们有两个表，一个是 orders（订单表），一个是 customers（客户表），我们希望在 orders表中添加一个外键，引用 customers表的 customer_id。-- 添加外键约束到orders表，引用customers表的customer_id

用法：


ALTER TABLE orders

ADD CONSTRAINT fk_orders_customers

FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

使用 `SHOW CREATE TABLE`确认添加

为了验证外键是否成功添加，我们可以使用 SHOW CREATE TABLE命令查看 orders表的定义：


SHOW CREATE TABLE orders;

输出会包含创建该表时使用的完整SQL语句，其中应该能看到刚添加的外键约束 fk_orders_customers。例如，输出可能类似这样：


CREATE TABLE `orders` (

  ...

  `customer_id` int(11) DEFAULT NULL,

  ...

  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

删除外键约束

如果需要删除上述添加的外键约束，可以使用以下命令：


ALTER TABLE orders

DROP FOREIGN KEY fk_orders_customers;

使用 `SHOW CREATE TABLE`确认删除

再次运行 SHOW CREATE TABLE orders;命令，这次在输出中应该找不到之前添加的外键约束 fk_orders_customers了，这表明外键已经被成功删除。

通过这种方式，你可以轻松地管理MySQL数据库中的外键约束，无论是添加还是删除，并且通过 SHOW CREATE TABLE命令来确认操作是否成功执行。

在MySQL中，ON DELETE和 ON UPDATE是外键约束的一部分，用于定义当主表（父表）中的数据发生变化时，如何影响从表（子表）中相关联的数据。这两个子句决定了当主表中的记录被删除或更新时，从表中外键字段的行为。以下是它们的详细说明：

ON DELETE

CASCADE: 当主表中的记录被删除时，从表中所有引用该记录的行也将被自动删除。这是一种级联效应，确保了数据的一致性，但需谨慎使用，因为它可能导致大量数据的删除。
SET NULL: 如果从表中的外键字段允许NULL值，那么当主表中对应的记录被删除时，从表中外键字段的值将被设置为NULL，解除与已删除记录的关联。
RESTRICT/NO ACTION: 这两个选项在MySQL中效果相同。当尝试删除主表中被从表引用的记录时，操作会被拒绝，以防止破坏外键约束，避免数据不一致。数据库会返回一个错误，删除操作不会被执行。
NO ACTION: 在某些数据库系统中，NO ACTION可能会在事务提交时检查约束，与 RESTRICT稍有不同，但在MySQL中两者等价，都立即检查并阻止违反约束的操作。

ON UPDATE

CASCADE: 类似于 ON DELETE CASCADE，当主表中的记录被更新时，从表中所有引用该记录的行的外键值也会被更新为新值，保持与主表同步。
SET NULL: 如果从表中的外键允许NULL值，当主表中对应的记录被更新时，从表中外键字段的值将被设置为NULL。
RESTRICT/NO ACTION: 在尝试更新主表中被从表引用的记录时，如果这会导致从表中外键引用失效（例如，更新主键值），则操作会被拒绝，以维持数据完整性。
NO ACTION: 同 ON DELETE中的解释，确保更新操作不会违反现有外键约束。

示例

假设有一个 departments表（主表）和一个 employees表（从表），其中 employees表中的 dept_id是外键，引用 departments表的 id。

要设置 ON DELETE CASCADE和 ON UPDATE CASCADE，可以这样写：

ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_id) REFERENCES departments(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

这样，如果一个部门被删除或更新，所有相关员工记录的 dept_id也会相应地被删除或更新。

选择合适的 ON DELETE和 ON UPDATE策略时，需要根据业务逻辑和数据完整性需求来决定，以确保数据库操作既高效又安全。

在MySQL中，外键约束是通过维护引用完整性来避免垃圾数据的产生，并且在处理主表数据删除时，可以通过设置不同的操作规则来决定从表数据的处理方式。下面是具体例子和使用方式：

避免垃圾数据的例子

假设有两个表：authors（作者表）和 books（书籍表），其中 books表中的 author_id是外键，引用 authors表的主键 id。


CREATE TABLE authors (

id INT PRIMARY KEY,

name VARCHAR(100) NOT NULL



);



CREATE TABLE books (

id INT PRIMARY KEY,

title VARCHAR(255) NOT NULL,

author_id INT,

FOREIGN KEY (author_id) REFERENCES authors(id)

);

在这个例子中，尝试在 books表中插入一条没有对应 authors表记录的 author_id会失败，从而避免了垃圾数据的产生。

删除主表数据时从表数据的处理方式

级联删除（CASCADE）

如果在删除 authors表中的记录时希望同时删除 books表中所有相关书籍记录，可以这样设置外键约束：
```
ALTER TABLE books

ADD FOREIGN KEY (author_id) REFERENCES authors(id)

ON DELETE CASCADE;
```
之后，当你删除一个作者时，与其相关的所有书籍记录也会被自动删除，不会报错。
限制（RESTRICT/NO ACTION）

这是默认行为，如果尝试删除一个在 books表中被引用的作者，MySQL会阻止删除操作并报错，以保护数据完整性。
```
-- RESTRICT 是默认选项，无需特别声明
```
设置为NULL（SET NULL）

如果 books表的 author_id允许为NULL，可以设置外键在主表记录删除时将其设为NULL。
```
ALTER TABLE books

ADD FOREIGN KEY (author_id) REFERENCES authors(id)

ON DELETE SET NULL;
```
这样，当删除一个作者时，相关书籍的 author_id会被设为NULL，书籍记录依然存在，但不再关联到任何作者。

结论

通过上述方法，MySQL的外键约束不仅能够避免垃圾数据的产生，还可以在删除主表数据时通过不同的策略来处理从表数据，确保数据的一致性和完整性。选择哪种处理方式取决于具体的应用需求和业务逻辑。

在MySQL中，外键约束的两种行为 NO ACTION和 RESTRICT在处理主表数据删除时有相似之处，但也存在细微差别，主要体现在数据库如何响应删除操作上，尤其是在涉及到外键引用完整性时。以下是这两种行为的详细解释和示例：

RESTRICT

定义: 当尝试删除主表中某个记录时，如果该记录在从表中被引用（即存在相关联的外键记录），RESTRICT行为会阻止删除操作。这是为了防止因删除而导致从表中出现孤立的外键引用，即数据不一致。
使用场景: 适用于那些需要严格维护数据完整性的场景，确保删除操作不会无意中破坏数据库中的引用关系。
示例:
```
ALTER TABLE books

ADD FOREIGN KEY (author_id) REFERENCES authors(id)

ON DELETE RESTRICT;
```
如果尝试删除一个在 authors表中被书籍引用的作者记录，操作会被拒绝，系统报错，以防止数据不一致。

NO ACTION

定义: MySQL中的 NO ACTION实际上等同于 RESTRICT。这意味着如果试图删除主表中一行数据，而该行在从表中有对应关联项，操作会被拒绝，以防止违反外键约束。尽管在一些数据库系统中 NO ACTION可能有延迟检查的特性，但在MySQL中它与 RESTRICT效果相同。
使用场景: 由于在MySQL中两者等效，因此使用场景也与 RESTRICT相同，即需要确保数据完整性的严格控制。
示例:
```
ALTER TABLE books

ADD FOREIGN KEY (author_id) REFERENCES authors(id)

ON DELETE NO ACTION;
```
同样，如果尝试删除一个被引用的作者记录，操作会被阻止，防止从表中出现无效的外键引用，报错信息与使用 RESTRICT时相似。

总结

在MySQL环境下，NO ACTION和 RESTRICT在处理外键约束时的行为是相同的，都是在删除主表数据前检查是否存在从表中的依赖，若有，则拒绝执行删除操作以防止数据不一致。因此，根据具体需求选择其一即可，它们都能有效防止因主表数据删除而导致的从表数据“悬挂”问题。

注意:

有外键约束，主表不能直接删除，从表有引用

主表，要先删除从表对应数据，再删除主表。

在MySQL中，外键约束（FOREIGN KEY constraint）用于确保主表和从表之间的关系一致性。当创建外键约束时，可以指定 ON DELETE和 ON UPDATE动作，以定义当主表中的行被删除或更新时，从表中受影响的行应如何处理。

关于 `ON DELETE`和 `ON UPDATE`选项：

ON DELETE:

CASCADE: 当主表中的行被删除时，从表中所有依赖于该行的行也将被自动删除。这可以防止孤儿记录的产生。
SET NULL: 当主表中的行被删除时，从表中依赖于该行的外键字段将被设置为NULL（前提是从表的外键字段允许NULL值）。
NO ACTION: 默认情况下，如果从表中有依赖的行，则不允许删除主表中的行。这可以防止意外删除导致的关系不一致。
RESTRICT: 与 NO ACTION相似，不允许删除主表中的行，如果从表中有依赖的行。这是 NO ACTION的同义词。
SET DEFAULT: 将从表中的外键字段设置为其默认值。如果字段有定义默认值的话。

ON UPDATE:

CASCADE: 当主表中的行被更新时，从表中所有依赖于该行的外键字段也将被更新为新的主键值。
SET NULL: 当主表中的行被更新时，从表中依赖于该行的外键字段将被设置为NULL（如果字段允许NULL值）。
NO ACTION: 不允许更新主表中的行，如果从表中有依赖于原主键值的行。
RESTRICT: 同 NO ACTION。
SET DEFAULT: 将从表中的外键字段设置为其默认值。

创建外键约束示例：

假设我们有两个表 authors和 books，authors表有 author_id作为主键，books表有 book_id作为主键和 author_id作为外键。

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(100)
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
    ON DELETE CASCADE  -- 当删除authors表中的行时，也删除books表中相应的行
    ON UPDATE CASCADE  -- 当更新authors表中的author_id时，也更新books表中的author_id
);

在这个例子中，如果 authors表中的某行被删除，那么 books表中所有引用该作者ID的行也将被删除，以保持数据一致性。同样，如果 authors表中的 author_id被更新，那么 books表中相应的 author_id也会被更新。

使用 ON DELETE和 ON UPDATE选项时，应根据具体业务逻辑和数据完整性需求来选择合适的动作。

第5章多表操作.pptx
https://www.alipan.com/s/ncMMHnK36jr
点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。

菜单

分享

定义

目的