SQL FOREIGN KEY

SQL FOREIGN KEY（外键）约束用于建立两个表之间的关系。外键是一个表中的列（或列组合），它引用另一个表中的主键或唯一约束列。

语法

创建表时添加外键约束

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column)
);

命名外键约束

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column)
);

表创建后添加外键约束

ALTER TABLE table_name
ADD FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column);

-- 命名约束
ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column);

移除外键约束

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

示例

示例 1：创建表时添加外键约束

假设我们有一个 students 表和一个 scores 表，scores 表中的 student_id 列引用 students 表中的 id 列。

-- 创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    grade VARCHAR(20)
);

-- 创建成绩表，添加外键约束
CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject VARCHAR(50) NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

执行结果：创建 students 表和 scores 表，并在 scores 表中添加外键约束，引用 students 表的 id 列。

示例 2：向有外键约束的表插入数据

-- 先插入学生数据
INSERT INTO students (name, age, grade)
VALUES ('张三', 18, '高三'),
       ('李四', 17, '高二');

-- 成功插入成绩数据（student_id 存在于 students 表中）
INSERT INTO scores (student_id, subject, score)
VALUES (1, '数学', 95.5),
       (1, '英语', 88.0),
       (2, '数学', 92.0);

-- 失败插入成绩数据（student_id 不存在于 students 表中）
INSERT INTO scores (student_id, subject, score)
VALUES (3, '数学', 85.0);

示例 3：删除被外键引用的数据

-- 失败删除学生数据（该学生的成绩存在于 scores 表中）
DELETE FROM students
WHERE id = 1;

-- 先删除相关的成绩数据
DELETE FROM scores
WHERE student_id = 1;

-- 再删除学生数据
DELETE FROM students
WHERE id = 1;

示例 4：表创建后添加外键约束

-- 创建表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    grade VARCHAR(20)
);

CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject VARCHAR(50) NOT NULL,
    score DECIMAL(5,2) NOT NULL
);

-- 添加外键约束
ALTER TABLE scores
ADD FOREIGN KEY (student_id) REFERENCES students(id);

示例 5：命名外键约束

-- 创建表时命名外键约束
CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject VARCHAR(50) NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES students(id)
);

-- 表创建后命名外键约束
ALTER TABLE scores
ADD CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES students(id);

外键的作用

1. 建立表之间的关系：外键约束用于建立两个表之间的关系，使数据库结构更加合理。

2. 确保数据完整性：外键约束确保引用的数据存在，防止无效数据的插入。

3. 级联操作：可以设置外键的级联操作，如级联删除、级联更新等。

外键的级联操作

外键可以设置以下级联操作：

级联操作	描述
CASCADE	当主键表中的记录被删除或更新时，外键表中的相关记录也会被删除或更新
SET NULL	当主键表中的记录被删除或更新时，外键表中的相关记录的外键值会被设置为 NULL
SET DEFAULT	当主键表中的记录被删除或更新时，外键表中的相关记录的外键值会被设置为默认值
NO ACTION	当主键表中的记录被删除或更新时，不执行任何操作（默认行为）
RESTRICT	当主键表中的记录被删除或更新时，如果外键表中存在相关记录，则拒绝执行操作

示例 6：设置外键的级联操作

CREATE TABLE scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    subject VARCHAR(50) NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE
);

执行结果：创建 scores 表，并设置外键的级联操作，当 students 表中的记录被删除或更新时，scores 表中的相关记录也会被删除或更新。

注意事项

1. 引用的列：外键引用的列必须是目标表的主键或唯一约束列。

2. 数据类型：外键列和被引用列的数据类型必须相同。

3. NULL 值：外键列可以为 NULL，但如果为 NULL，则不进行引用检查。

4. 性能影响：外键约束会增加数据库的开销，可能会影响插入、更新和删除操作的性能。

5. 索引：建议在外键列上创建索引，以提高查询性能。

小结

SQL FOREIGN KEY 约束是一种用于建立表之间关系的重要约束。它确保外键表中的值必须存在于被引用表中，从而保证数据的完整性和一致性。外键还可以设置级联操作，以自动处理相关记录的删除或更新。在数据库设计中，合理使用外键约束可以使数据库结构更加清晰和合理。