SQL 表关系详解

深入剖析数据库中的一对一、一对多、多对多关系及实现

一对一关系 (One-to-One)

             **定义：** 表 A 中的一条记录只能对应表 B 中的一条记录，反之亦然。常用于拆分宽表以提升性能或安全性。

-- 方式一：共享主键 (Profile.id 即是 PK 也是 FK)
CREATE TABLE user_profile (
    user_id INT PRIMARY KEY,
    address VARCHAR(200),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- 方式二：独立外键 (需加 UNIQUE 约束)
CREATE TABLE user_detail (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE, -- 关键：Unique 保证 1对1
    FOREIGN KEY (user_id) REFERENCES user(id)
);

• 必须在其中一个表的外键列上添加 UNIQUE 约束（方式二）。

• 常用于垂直分表：将高频访问字段和低频大字段（如自我介绍）分离。

• 两种方式均能保证数据一致性，共享主键方式更节省空间。

一对多关系 (One-to-Many)

             **定义：** 最常见的关系。A 表一条记录对应 B 表多条记录，但 B 表一条记录仅对应 A 表一条。

-- 1. 创建"一"的一方 (销售员)
CREATE TABLE salesperson (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 2. 创建"多"的一方 (订单)，添加外键指向"一"
CREATE TABLE orders (
    id INT PRIMARY KEY,
    amount DECIMAL(10, 2),
    salesperson_id INT,
    FOREIGN KEY (salesperson_id) REFERENCES salesperson(id)
);

• 原则：在"多"的一方维护外键。

• 如果业务上允许订单没有归属，外键可以设置为 NULL。

多对多关系 (Many-to-Many)

             **定义：** A 表记录对应多个 B 表记录，反之亦然。例如：学生与课程、文章与标签。

-- 需要通过"中间表"来解耦
CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id), -- 联合主键
    FOREIGN KEY (student_id) REFERENCES student(id),
    FOREIGN KEY (course_id) REFERENCES course(id)
);

• 必须引入第三张表（关联表/中间表）。

• 中间表可以包含额外属性，例如：选课时间、考试成绩。

• 联合主键可防止重复关联相同的数据（如同一学生重复选同一课）。

关系模型总结

                    关系类型
                    实现核心
                    典型场景
                
            
            
                
                    一对多 (1:N)
                    在 N 端加外键
                    用户-订单，部门-员工，分类-商品
                
                
                    一对一 (1:1)
                    外键加 UNIQUE 约束
                    用户-身份证信息，主表-扩展表
                
                
                    多对多 (N:M)
                    中间表 + 双外键
                    学生-课程，角色-权限，作者-书籍

进阶：物理外键 vs 逻辑外键

数据库设计中的经典权衡：是依赖数据库约束，还是依赖代码逻辑？

                    特性
                    物理外键 (DBMS Constraint)
                    逻辑外键 (Application Logic)
                
            
            
                
                     **数据完整性** 
                    极高 - 数据库强制保证，杜绝脏数据
                    中等 - 依赖代码健壮性，Bug可能导致数据不一致
                
                
                     **写入性能** 
                    略低 - 插入/删除时需检查约束，有锁开销
                    高 - 无额外数据库检查开销
                
                
                     **架构耦合** 
                    高 - 分库分表困难，难以水平扩展
                    低 - 适合分布式架构和微服务
                
                
                     **级联操作** 
                    支持 (Cascade Delete/Update)
                    需手动编写代码逻辑处理
                
                
                     **推荐场景** 
                     **传统单体应用、数据敏感、后台管理系统** 
                     **互联网高并发、分库分表、微服务架构** 

附录：主流数据库类型速查

不同数据库在实现相同概念时，数据类型名称往往不同。

                    类型
                    MySQL
                    Oracle
                    SQL Server
                
            
            
                
                     **变长字符串** 
                     `VARCHAR(N)` 
                     `VARCHAR2(N)` 
                     `VARCHAR(N)` 
                
                
                     **时间日期** 
                     `DATETIME` 
                     `DATE`  (含时分秒)
                     `DATETIME2` 
                
                
                     **大整数** 
                     `BIGINT` 
                     `NUMBER(19)` 
                     `BIGINT` 
                
                
                     **自动增长** 
                     `AUTO_INCREMENT` 
                     `SEQUENCE` 
                     `IDENTITY`