mysql 设置主键外键关联

基础概念

MySQL中的主键（Primary Key）和外键（Foreign Key）是关系型数据库中用于维护数据完整性和一致性的约束。

主键：主键是表中的一个或多个字段，其值能唯一地标识表中的每一行记录。主键的作用是确保数据的唯一性和完整性。
外键：外键是表中的一个字段或字段组合，其值引用另一个表的主键。外键的作用是建立两个表之间的关联，确保引用完整性。

类型

单字段主键：使用单个字段作为主键。
复合主键：使用多个字段组合作为主值。
单字段外键：使用单个字段引用另一个表的主键。
复合外键：使用多个字段组合引用另一个表的复合主键。

应用场景

订单管理系统：订单表和客户表可以通过外键关联，方便查询某个客户的订单信息。
学生管理系统：学生表和班级表可以通过外键关联，方便查询某个班级的学生信息。
库存管理系统：商品表和库存表可以通过外键关联，方便查询某个商品的库存情况。

示例代码

假设有两个表：students（学生表）和classes（班级表），其中students表中的class_id字段是外键，引用classes表中的id字段（主键）。

创建班级表（classes）

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

创建学生表（students）

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

插入数据

-- 插入班级数据
INSERT INTO classes (name) VALUES ('Class A');
INSERT INTO classes (name) VALUES ('Class B');

-- 插入学生数据
INSERT INTO students (name, class_id) VALUES ('Alice', 1);
INSERT INTO students (name, class_id) VALUES ('Bob', 1);
INSERT INTO students (name, class_id) VALUES ('Charlie', 2);

查询数据

-- 查询某个班级的学生信息
SELECT students.name AS student_name, classes.name AS class_name
FROM students
JOIN classes ON students.class_id = classes.id
WHERE classes.name = 'Class A';

常见问题及解决方法

问题：外键约束导致插入失败

原因：插入的数据违反了外键约束，即引用的主键不存在。

解决方法：确保插入的数据在引用表中存在相应的主键值。

-- 错误示例
INSERT INTO students (name, class_id) VALUES ('David', 3); -- class_id 3 不存在

-- 正确示例
INSERT INTO classes (name) VALUES ('Class C');
INSERT INTO students (name, class_id) VALUES ('David', 3); -- 现在 class_id 3 存在

问题：删除主键数据导致外键约束失败

原因：删除主键数据时，如果存在引用该主键的外键数据，会导致外键约束失败。

解决方法：在删除主键数据前，先删除或更新引用该主键的外键数据。

-- 删除班级前，先删除引用该班级的学生
DELETE FROM students WHERE class_id = 1;
DELETE FROM classes WHERE id = 1;

或者使用级联删除：

-- 创建外键时指定 ON DELETE CASCADE
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
);

这样删除班级时，会自动删除引用该班级的学生。