okgoes
🔍
← 目录从入门到精通MySQL
✏️ 编辑

MySQL外键

2024/11/2

MySQL 外键详解

在关系型数据库中，外键是用于建立和强化两个数据表之间的链接的一种约束。外键确保了数据的一致性和完整性。本文将详细介绍 MySQL 中外键的定义与使用、外键约束的类型与效果，以及外键的级联操作（CASCADE、SET NULL、NO ACTION），并结合示例代码进行说明。

一、外键的定义与使用

1. 外键的定义

外键是一个表中的字段，它用于引用另一个表的主键。通过这种方式，可以在两个表之间建立起关联。

2. 使用外键

在创建表时，可以使用 FOREIGN KEY 约束来定义外键。以下是一个简单的示例：

sql
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

在这个例子中，employees 表中的 department_id 字段是一个外键，引用了 departments 表中的 id 字段。这样可以确保每个员工都属于一个有效的部门。

二、外键约束的类型与效果

外键约束主要有以下几种类型，每种类型都有不同的效果：

1. CASCADE

如果引用的行被删除或更新，所有引用该行的行也会自动删除或更新。

sql
CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE
);

在这个例子中，如果一个部门被删除，所有属于该部门的项目也会被自动删除。

2. SET NULL

如果引用的行被删除或更新，所有引用该行的行中对应的外键字段将被设置为 NULL。

sql
CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    project_id INT,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
);

如果一个项目被删除，所有与该项目相关的任务的 project_id 将被设置为 NULL。

3. NO ACTION

如果引用的行被删除或更新，而存在引用该行的外键，数据库将拒绝执行该操作。这是默认的行为。

sql
CREATE TABLE assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    project_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE NO ACTION,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE NO ACTION
);

如果试图删除一个员工或项目，而仍然有与之关联的分配记录，数据库将返回错误。

三、外键的级联操作

1. CASCADE 示例

假设我们有部门和员工的表，并且希望在删除部门时，所有属于该部门的员工都被删除。

sql
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

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

-- 插入数据
INSERT INTO departments (name) VALUES ('HR'), ('Engineering');
INSERT INTO employees (name, department_id) VALUES ('Alice', 1), ('Bob', 2);

-- 删除部门 HR
DELETE FROM departments WHERE id = 1;

-- 此时，Alice 也会被自动删除
SELECT * FROM employees;

2. SET NULL 示例

假设我们有一个项目和任务的表，如果删除项目时，相关的任务的 project_id 需要设置为 NULL。

sql
CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    project_id INT,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
);

-- 插入数据
INSERT INTO projects (name) VALUES ('Project A'), ('Project B');
INSERT INTO tasks (title, project_id) VALUES ('Task 1', 1), ('Task 2', 1), ('Task 3', 2);

-- 删除项目 A
DELETE FROM projects WHERE id = 1;

-- 此时，Task 1 和 Task 2 的 project_id 会被设置为 NULL
SELECT * FROM tasks;

3. NO ACTION 示例

假设我们有员工和分配的表，试图删除一个仍被引用的员工时，将会产生错误。

sql
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE NO ACTION
);

-- 插入数据
INSERT INTO employees (name) VALUES ('Alice'), ('Bob');
INSERT INTO assignments (employee_id) VALUES (1);

-- 尝试删除 Alice
DELETE FROM employees WHERE id = 1; -- 此时将会报错

四、外键的查询和管理

1. 查询外键约束

可以使用 SHOW CREATE TABLE 来查看表的外键约束信息。

sql
SHOW CREATE TABLE employees;

2. 删除外键约束

如果需要删除外键约束，可以使用 ALTER TABLE。

sql
ALTER TABLE employees DROP FOREIGN KEY fk_department_id; -- 假设外键名为 fk_department_id

3. 添加外键约束

同样可以使用 ALTER TABLE 来添加外键约束。

sql
ALTER TABLE employees ADD FOREIGN KEY (department_id) REFERENCES departments(id);

总结

通过本文，你学习了 MySQL 中外键的定义与使用、外键约束的类型与效果，以及外键的级联操作（CASCADE、SET NULL、NO ACTION）。合理使用外键可以有效维护数据库的完整性和一致性，从而提高数据的可靠性。