一、外键概述

1 什么是外键

外键（Foreign Key）是数据库表中的一列或多列，用于建立表之间的链接，通过这些链接，可以强制实施引用完整性，确保数据的一致性和准确性，在关系型数据库中，外键可以引用同一个数据库中另一个表的主键，从而建立起两个表之间的关联关系，这种关系不仅有助于维护数据的完整性，还能有效防止孤立记录的产生。

2 外键的作用

数据一致性

外键通过引用主键来确保数据的一致性，在一个订单系统中，订单表中的客户ID应引用客户表中的客户ID，如果客户ID在客户表中不存在，则不能在订单表中插入这样的客户ID，这有效地防止了无效数据的插入，确保了数据的一致性和准确性。

参照完整性

外键约束帮助维护参照完整性，即确保外键的值必须在被引用的表的主键中存在，在学生管理系统中，选课表中的课程ID必须是课程表中已存在的课程ID，否则无法插入该记录，这样，外键机制保证了表之间的数据同步和一致，避免了孤立或无效数据的生成。

级联操作

MySQL支持级联操作，包括级联删除（CASCADE DELETE）和级联更新（CASCADE UPDATE），当父表中的记录被删除或更新时，子表中的关联记录也会自动删除或更新，当一个客户从客户表中删除时，所有属于该客户的订单也会自动删除，这种自动化的操作简化了数据管理流程，减少了人工干预的需求。

3 外键的类型

MySQL支持多种外键类型，每种类型在不同场景下有不同的应用：

RESTRICT

RESTRICT类型的外键在删除或更新父表记录时，会拒绝那些会影响到与其关联的子表记录的操作，如果试图删除或更新的父表记录在子表中存在关联记录，操作将被禁止并返回错误，这种类型主要用于保护数据的完整性，避免因误操作导致的数据丢失或不一致。

CASCADE

CASCADE类型的外键在父表记录删除或更新时，会自动删除或更新子表中的关联记录，这种级联操作确保了数据的同步和一致性，适用于那些需要自动清理关联数据的场景，当删除一个分类时，其下所有的子分类也一并删除。

SET NULL

SET NULL类型的外键在父表记录删除或更新时，将子表中的外键字段设置为NULL，这种操作适用于那些允许空值的外键字段，且业务逻辑上可以接受将关联记录置为空的情形，当一个客户退订账户后，其订单记录中的客户ID可以被设为NULL。

NO ACTION

NO ACTION类型的外键与RESTRICT类似，但在违反约束时不会立即回滚操作，而是持有违例操作直到事务提交才进行检查，如果在事务结束时发现有约束冲突，则会回滚整个事务，这种类型提供了一种更为灵活的约束机制，适用于需要在事务中进行复杂操作的场景。

二、使用ALTER TABLE语句添加外键

1 基本语法

使用ALTER TABLE语句可以在已有表上添加外键，以下是基本的语法结构：

ALTER TABLE 子表名
ADD CONSTRAINT 外键名称 FOREIGN KEY (子表列名)
REFERENCES 父表名 (父表列名);

2 示例

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

创建示例表：

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255)
);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT
);

添加外键：

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);

3 注意事项

数据类型一致：外键列和被引用列的数据类型必须一致，如果数据类型不同，可能会导致隐式转换或者直接报错。

索引要求：被引用的列必须是一个索引列（通常是主键），如果没有索引，查询性能可能会受到影响。

现有数据验证：在添加外键之前，确保子表中的数据在父表中存在，否则，添加外键约束会失败，可以通过以下方式检查：

    SELECT * FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.customer_id
    WHERE c.customer_id IS NULL;

如果有返回结果，说明存在不合规的数据。

三、通过图形化工具添加外键

1 phpMyAdmin

登录phpMyAdmin

打开浏览器，输入phpMyAdmin的访问地址，输入用户名和密码登录。

选择数据库和表

在左侧面板中选择要操作的数据库，然后点击展开表列表，选择需要添加外键的表。

添加外键步骤

1、点击“结构”标签页，然后找到并点击“关系视图”按钮。

2、在“关系视图”页面中，选择要添加外键的列（如customer_id）。

3、在弹出的对话框中，选择引用的表（如customers）和列（如customer_id）。

4、选择外键的动作（如CASCADE），然后点击“保存”按钮。

5、系统会提示确认，点击“确定”。

6、刷新表结构，可以看到外键已经添加成功。

2 MySQL Workbench

打开MySQL Workbench

启动MySQL Workbench并连接到数据库服务器。

选择数据库和表

在左侧面板中选择要操作的数据库，然后右键点击目标表，选择“Alter Table”。

添加外键步骤

1、在“Foreign Keys”选项卡中，点击“Add Foreign Key”按钮。

2、填写外键名称（如fk_customer），选择引用的表（如customers）和列（如customer_id）。

3、选择自身的被引用列（如customer_id），并根据需要选择级联操作（如ON DELETE CASCADE和ON UPDATE CASCADE）。

4、点击“Apply”按钮，然后点击“Apply”再次确认更改。

5、完成之后，刷新表结构即可看到新添加的外键。

四、通过脚本自动化添加外键

1 编写存储过程

在某些情况下，通过脚本自动化地添加多个外键可以提高操作效率，以下是一个示例存储过程，用于在多个表上添加外键：

DELIMITER $$
CREATE PROCEDURE AddForeignKeys()
BEGIN
    -- Declare a cursor for table names
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE col_name VARCHAR(255);
    DECLARE fk_name VARCHAR(255);
    DECLARE ref_tbl_name VARCHAR(255);
    DECLARE ref_col_name VARCHAR(255);
    DECLARE cur CURSOR FOR
        SELECT table_name, column_name, constraint_name, referenced_table_name, referenced_column_name
        FROM information_schema.key_column_usage
        WHERE referenced_table_name IS NOT NULL AND referenced_table_name != table_name;
    -- Declare continue handler for cursor
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- Open the cursor
    OPEN cur;
    read_loop: LOOP
        -- Fetch data from the cursor into variables
        FETCH cur INTO tbl_name, col_name, fk_name, ref_tbl_name, ref_col_name;
        -- If no more data, exit loop
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- Build the ALTER TABLE statement dynamically based on fetched data
        SET @stmt = CONCAT('ALTER TABLE ', tbl_name, ' ADD CONSTRAINT ', fk_name, ' FOREIGN