MySQL 外键约束

 外键约束核心概念

基本定义

主表（父表）：被引用的表，包含主键

从表（子表）：包含外键的表，引用主表的主键

参照完整性：确保外键值必须匹配主表中存在的值或为NULL

外键约束规则

1. 主表必须存在且已定义主键

2. 外键列和主键列数据类型必须兼容

3. 外键列允许NULL值（除非明确设置NOT NULL）

4. 外键列和主键列的数目必须相同

5. InnoDB存储引擎支持外键（MyISAM不支持）

外键约束操作详解

创建时定义外键

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    CONSTRAINT fk_customer
        FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE SET NULL
);

修改表添加外键

ALTER TABLE order_items
ADD CONSTRAINT fk_order
    FOREIGN KEY (order_id)
    REFERENCES orders(order_id)
    ON DELETE RESTRICT;

删除外键约束

ALTER TABLE orders DROP FOREIGN KEY fk_customer;

外键约束的级联操作

MySQL支持5种引用操作，用于定义主表记录变更时的行为：

示例：

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    CONSTRAINT fk_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON DELETE CASCADE
        ON UPDATE RESTRICT,
    CONSTRAINT fk_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE SET NULL
);

外键约束的高级特性

自引用外键

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    CONSTRAINT fk_manager
        FOREIGN KEY (manager_id)
        REFERENCES employees(emp_id)
        ON DELETE SET NULL
);

列外键

CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_order_product
        FOREIGN KEY (order_id, product_id)
        REFERENCES orders_products(order_id, product_id)
);

外键索引

 MySQL会自动在外键列上创建索引（如果不存在）

 删除外键时不会自动删除该索引

外键约束的最佳实践

1. 命名规范：使用`fk_从表_主表`的命名约定

2. 性能考虑：

    外键会带来一定的性能开销

    在频繁写入的场景中需谨慎使用

3. 设计原则：

    避免过深的引用层次（如超过3层）

    考虑使用逻辑外键（应用层控制）替代物理外键

4. 开发环境：

    在测试环境中验证级联操作的影响

    使用`SET FOREIGN_KEY_CHECKS=0`临时禁用约束检查（仅限特殊场景）

外键约束的常见问题排查

1. 错误1452：无法添加或更新子行，外键约束失败

    检查插入的值是否存在于主表中

    检查数据类型是否匹配

2. 错误1215：无法添加外键约束

    确认主表存在且有主键

    确认数据类型完全匹配

3. 错误150：外键约束格式不正确

    检查语法是否正确

    确保存储引擎是InnoDB

实际应用示例

电商数据库设计：

 主表

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
) ENGINE=InnoDB;

从表

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2),
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

多对多关系中的外键

CREATE TABLE order_products (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_op_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_op_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE RESTRICT
) ENGINE=InnoDB;