数据库-外键

承接Qt/C++软件开发项目，高质量交付，灵活沟通，长期维护支持。需求所寻，技术正适，共创完美，欢迎私信联系！

一、外键说明

        外键在数据库中起到了关联表之间关系的作用，它定义了一个表中的字段与另一个表中的字段之间的关联关系。在设计和使用外键时，有几个重要的方面需要注意：

        数据完整性约束：外键用于确保关联数据的完整性，它可以防止不一致或无效的数据插入到关联表中。这样可以保证数据库中的数据一致性。

索引：在大多数数据库管理系统中，外键字段通常会自动创建索引。这样可以提高查询的性能，但也会增加插入、更新和删除操作的开销。因此，在设计表结构时，需要权衡索引的使用。

        级联操作：在定义外键关系时，可以选择设置级联操作，例如当主表中的记录被删除或更新时，相关的从表记录可以被自动删除、更新或设置为 NULL。正确使用级联操作可以简化数据维护，但也需要小心避免意外的数据损失。

        约束名称：为外键约束定义有意义的名称是良好的数据库设计实践之一，它有助于理解和维护数据库结构。约束名称应该清晰地反映出外键的作用和含义。

        性能影响：在大型数据库中，外键约束可能会对性能产生一定的影响，特别是在进行大量数据插入、更新或删除操作时。因此，需要仔细考虑外键的使用方式，以确保不会对系统性能造成不良影响。

        数据加载和备份：在进行数据加载或备份时，外键约束可能会影响操作的速度和复杂性。因此，在执行这些操作之前，可能需要考虑临时禁用外键约束。

        总的来说，正确使用外键可以提高数据库的数据完整性和一致性，并且有助于减少开发和维护的复杂性。然而，需要注意外键的设计和使用可能带来的性能影响，并根据具体情况进行权衡和调整

二、外键和外键约束的区别

        在数据库中，"外键"（Foreign Key）和"外键约束"（Foreign Key Constraint）通常是指相同的概念，但它们略有不同的侧重点。

        外键（Foreign Key）：是指一个表中的字段，它与另一个表中的字段相关联，用于定义这两个表之间的关系。外键字段包含对另一个表中的主键或唯一键的引用。

        外键约束（Foreign Key Constraint）：是指一组规则，用于确保外键的数据完整性。外键约束定义了外键字段与相关联表中的主键或唯一键之间的关系，并规定了对外键字段进行插入、更新和删除操作时所需要遵循的规则。

        因此，外键约束通常是对外键的一种限制或约束，用于确保数据库中数据的一致性和完整性。外键字段可以在没有外键约束的情况下存在，但如果需要强制执行数据完整性，则需要定义外键约束。

三、外键约束类型

在创建外键约束时，可以使用 ON DELETE 子句来指定级联操作的行为。常见的级联操作包括：

• CASCADE：级联删除，当引用表中的记录被删除时，自动删除关联的记录。
• SET NULL：设置为 NULL，当引用表中的记录被删除时，将关联的字段值设置为 NULL。
• RESTRICT：拒绝删除，如果有关联的记录存在，则拒绝删除引用表中的记录。
• NO ACTION：默认行为，与 RESTRICT 相同，如果有关联的记录存在，则拒绝删除引用表中的记录。

-- 创建 student_info 表，并定义 class_id 字段为外键，设置级联删除操作
CREATE TABLE student_info (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  class_id INT,
  FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE CASCADE
);

-- 如果需要修改已有的外键约束的级联操作行为，可以使用 ALTER TABLE 语句来修改。例如：
ALTER TABLE student_info
DROP FOREIGN KEY fk_class_id,
ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE CASCADE;

四、案例

1. students_info 表

Column Name	Data Type	Description
student_id	INT	学生唯一标识符（主键）
name	VARCHAR	学生姓名
age	INT	学生年龄
class_id	INT	学生所在班级的ID（外键）

1. classes 表

Column Name	Data Type	Description
class_id	INT	班级唯一标识符（主键）
grade_id	INT	年级ID（外键）

-- 创建 student_info 表
CREATE TABLE student_info (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  class_id INT,
  FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

-- 创建 classes 表
CREATE TABLE classes (
  class_id INT AUTO_INCREMENT PRIMARY KEY,
  grade_id INT,
  FOREIGN KEY (grade_id) REFERENCES grades(grade_id)
);

// 当我们在 student_info 表中插入数据时，如果插入的 class_id 在 classes 
// 表中找不到对应的值， 那么数据库会阻止这次插入操作，从而确保了数据的完整性。
// 这就是外键的作用：通过约束确保关联表之间的数据一致性。

// 如果在 classes 表中将 class_id 从 1 更新为 2，并且 student_info 表中已经
// 存在具有 class_id 为 1 的数据，那么这些数据在更新后的情况下，将保持不变。
// 外键约束只影响到将来的数据插入、更新和删除操作，对已经存在的数据不会产生影响。

-- 向 student_info 表插入数据
INSERT INTO student_info (name, age, class_id) VALUES ('Alice', 18, 1);
INSERT INTO student_info (name, age, class_id) VALUES ('Bob', 17, 1);

-- 从 student_info 表中删除指定条件的数据
DELETE FROM student_info WHERE name = 'Alice';

-- 修改 student_info 表中的数据
UPDATE student_info SET age = 19 WHERE name = 'Bob';

-- 查询 student_info 表中所有数据
SELECT * FROM student_info;

-- 查询 student_info 表中指定条件的数据
SELECT * FROM student_info WHERE age > 18;

-- 开始事务
START TRANSACTION;

-- 尝试删除班级对应的学生
DELETE FROM student_info WHERE class_id = (SELECT class_id FROM classes WHERE grade_id = 1);

-- 检查是否成功删除了学生
SELECT COUNT(*) FROM student_info WHERE class_id = (SELECT class_id FROM classes WHERE grade_id = 1);

-- 如果没有成功删除学生，则回滚事务
IF @@ROWCOUNT = 0 THEN
    ROLLBACK;
    -- 输出错误信息或执行其他操作
    SELECT '删除学生失败，事务已回滚。';
ELSE
    -- 如果成功删除学生，则继续删除班级信息
    DELETE FROM classes WHERE grade_id = 1;
    -- 提交事务
    COMMIT;
END IF;