面试题：MySQL中外键约束与参照完整性基础

创建带有外键约束表的SQL语句示例

假设我们有两个表，departments（部门表）和employees（员工表），employees表中的department_id字段需要引用departments表中的id字段作为外键。

-- 创建部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL
);

-- 创建员工表，并设置外键约束
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    -- 设置外键约束
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

外键约束维护参照完整性的原理

插入操作：当向employees表插入数据时，如果department_id的值在departments表的id列中不存在，插入操作将失败。这确保了员工表中的部门ID总是对应于实际存在的部门，避免无效数据插入。
更新操作：如果尝试更新departments表中id的值（被employees表作为外键引用），而employees表中存在对应的department_id引用，更新操作会失败，除非先更新employees表中的相关department_id值，使其符合新的departments表中的id值。这防止了数据引用的不一致。
删除操作：如果尝试删除departments表中的一条记录，而该记录的id被employees表中的department_id引用，删除操作会失败。这保证了在存在相关员工记录时，不会意外删除部门记录，从而维护了数据的一致性和参照完整性。

可以通过设置ON DELETE和ON UPDATE选项来改变这种默认行为，例如ON DELETE CASCADE，当删除departments表中的记录时，与之关联的employees表中的记录也会被自动删除；ON UPDATE CASCADE则在更新departments表中的id值时，自动更新employees表中的department_id值。示例如下：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

星途面试题库

面试题：MySQL中外键约束与参照完整性基础

知识考点

面试题答案

创建带有外键约束表的SQL语句示例

外键约束维护参照完整性的原理