MySQL数据库约束与表关系设计详解

核心数据库约束类型与应用

第1步：设置字段非空约束 (NOT NULL)

目的：确保指定列必须包含有效数据，防止出现NULL值导致的逻辑错误。 操作：在创建表时，为需要强制填值的列添加 NOT NULL 关键字。

CREATE TABLE student (
    id INT NOT NULL,
    name VARCHAR(20)
);

执行后查看表结构，可发现 id 列的 Null 属性显示为 NO，表示该列不允许为空。

此方法适用于必须记录的关键身份信息，如用户ID或订单编号。

第2步：配置唯一性约束 (UNIQUE)

目的：保证某列的数据在整个表中具有唯一性，插入重复值将触发报错。 操作：在列定义后附加 UNIQUE 约束。注意：NULL值通常不影响唯一性检查（取决于具体引擎配置），但实际数据不可重复。

此约束常用于邮箱、手机号等需要防止重复注册的场景。

第3步：设定默认值约束 (DEFAULT)

目的：当插入记录未指定该列的值时，自动使用预设值填充。 操作：在列定义中使用 DEFAULT '值'。

CREATE TABLE student (
    id INT,
    name VARCHAR(20) DEFAULT '匿名'
);

若插入语句省略 name 字段，系统会自动填入“匿名”。这减少了客户端应用层的冗余代码。

第4步：建立主键约束 (PRIMARY KEY) 与自增

目的：主键是 NOT NULL 与 UNIQUE 的结合体，用于唯一标识每一行数据，且必须有且仅有一个主键。
特性：搭配 AUTO_INCREMENT 可实现数值自动递增。

• 起始规则：若表为空，从 1 开始；若有数据，从最大现有值 +1 开始。
• 删除影响：删除已存在的记录后，自增值不会回收复用，而是继续向上递增。

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- 插入测试数据
INSERT INTO student VALUES (NULL, '张三');
SELECT * FROM student;

结果中 id 为 1，name 为 “张三”。执行 DESC 可查看 Extra 列为 auto_increment，Key 列为 PRI。

第5步：定义外键约束 (FOREIGN KEY)

目的：维持参照完整性，确保当前表中的数据必须在父表中有对应记录。这建立了两张表之间的依赖关系，防止产生孤立数据。 风险提醒：若父表记录被删除而子表仍引用它，直接删除父表会失败。需先处理子表数据或配置级联删除（CASCADE）。

-- 创建父表：班级表
CREATE TABLE class (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL
);

-- 创建子表：学生表，classId 作为外键关联 class(id)
CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    email VARCHAR(20) DEFAULT 'unknow',
    QQ VARCHAR(20) UNIQUE,
    classId INT,
    FOREIGN KEY (classId) REFERENCES class(id)
);

查看 student 表结构，classId 列的 Key 显示为 MUL（表示多值索引/外键），说明外键已成功绑定。此时无法向 student 插入一个 classId 在 class 表中不存在的值。

第6步：关于条件检查约束 (CHECK) 的注意事项

目的：指定复杂逻辑条件来判定值是否合法（如性别只能是“男”或“女”）。

CREATE TABLE test_user (
    id INT,
    name VARCHAR(20),
    sex VARCHAR(1),
    CHECK (sex = '男' OR sex = '女')
);

重要提示：在 MySQL 8.0.16 之前，CHECK 约束会被解析但被忽略，不生效。虽然新版已开始支持，但在早期版本或跨版本兼容设计中，建议在应用层或使用触发器进行数据校验，以免产生“约束存在但未拦截非法数据”的误解。

常见表关系设计与实践

第1步：实现一对一关系 (One-to-One)

场景：学生信息与扩展账户信息，一个学生对应一个账号，反之亦然。

设计方案：

1. 合并表：如果关系紧密且经常一起查询，可直接合并为一张表。
2. 分离表 + 外键：保留两张表，在其中一张表中添加另一张表的主键作为外键（并设为 UNIQUE）。这样既保持数据结构清晰，又强制了一对一的对应关系。

第2步：实现一对多关系 (One-to-Many)

场景：一个班级包含多个学生，一个学生只能属于一个班级。

设计方案：遵循“多端存一端ID”的原则。

1. 错误做法：在班级表中存储所有学生ID列表。数据库列不支持数组类型（除特定JSON或Redis场景外），这将导致数据冗余和更新困难。
2. 正确做法：在 student 表中新增 classId 列，将其设置为 FOREIGN KEY 指向 class 表的 id。查询时通过 JOIN 关联即可。

第3步：实现多对多关系 (Many-to-Many)

场景：学生选课，一个学生选多门课，一门课被多个学生选。

设计方案：引入中间关联表（Join Table）。

1. 建立实体表：test_student（学生）和 test_course（课程）。
2. 建立关联表：test_score，包含 studentId 和 courseId，分别作为外键关联前两个表的主键。

-- 1. 创建学生表和课程表
CREATE TABLE test_student (
    id INT PRIMARY KEY,
    name VARCHAR(10) DEFAULT 'unknow'
);

CREATE TABLE test_course (
    id INT PRIMARY KEY,
    name VARCHAR(20) DEFAULT 'unknow'
);

-- 2. 创建成绩关联表
CREATE TABLE test_score (
    studentId INT,
    courseId INT,
    score INT,
    -- 设置复合外键
    FOREIGN KEY (studentId) REFERENCES test_student(id),
    FOREIGN KEY (courseId) REFERENCES test_course(id)
);

数据写入示例：

-- 插入学生和课程
INSERT INTO test_student VALUES (1, 'listen'), (2, 'Faker');
INSERT INTO test_course VALUES (1, '数学'), (2, '语文');

-- 插入选课成绩 (listen学数学90分, 选语文99分; Faker学数学50分, 选语文60分)
INSERT INTO test_score VALUES 
(1, 1, 90), (1, 2, 99),
(2, 1, 50), (2, 2, 60);

查询 test_score 表即可看到每个学生的具体选课得分。这种模式灵活地解决了复杂的组合关系，避免了在主表中的重复存储和多值字段问题。

以上就是MySQL数据库约束与表关系设计的详细内容，更多关于MySQL优化与架构的资料请关注本站其它相关文章！