mysql建立表间关系

基础概念

MySQL中的表间关系是指两个或多个表之间的逻辑关联。这些关系通常通过外键（Foreign Key）来实现，用于确保数据的一致性和完整性。常见的表间关系类型包括：

一对一（One-to-One）：一个表中的记录与另一个表中的一个记录相关联。
一对多（One-to-Many）：一个表中的一个记录可以与另一个表中的多个记录相关联。
多对多（Many-to-Many）：一个表中的一个记录可以与另一个表中的多个记录相关联，反之亦然。

类型

一对一关系

例如，一个用户表和一个用户详情表：

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE user_details (
    detail_id INT PRIMARY KEY,
    user_id INT,
    email VARCHAR(100),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

一对多关系

例如，一个部门表和一个员工表：

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    employee_name VARCHAR(50),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

多对多关系

例如，一个学生表和一个课程表，通过一个中间表来关联：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

应用场景

电子商务系统：用户表和订单表之间的一对多关系。
社交网络：用户表和好友表之间的多对多关系。
内容管理系统：文章表和标签表之间的多对多关系。

常见问题及解决方法

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

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

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

-- 错误示例
INSERT INTO user_details (detail_id, user_id, email) VALUES (1, 100, 'test@example.com');

-- 正确示例
INSERT INTO users (user_id, username) VALUES (100, 'testuser');
INSERT INTO user_details (detail_id, user_id, email) VALUES (1, 100, 'test@example.com');

问题：删除主键记录导致外键约束冲突

原因：删除主键记录时，引用了该记录的外键记录未被处理。

解决方法：使用级联删除（CASCADE DELETE）或设置外键约束为ON DELETE SET NULL。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    employee_name VARCHAR(50),
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE
);