MYSQL：数据库约束-EW帮帮网

文章目录

MYSQL：数据库约束：为你的数据上把“安全锁”

MYSQL：数据库约束：为你的数据上把“安全锁”

大家好！今天我们来聊一个数据库中非常重要，但又常常被初学者忽视的概念——数据库约束。

想象一下，我们正在录入一个班级的学生信息。如果有人不小心把“姓名”这一栏漏填了，或者把两个同学的学号填成了一样的，那数据不就乱套了吗？为了保证存入数据库的数据是准确、可靠的，我们就需要给表中的数据定一些“规矩”。这些规矩，就是我们今天要聊的“数据库约束”。

简单来说，约束就是作用于表中列的规则，用于限制存储在列中的数据。它就像一把把安全锁，能从源头上防止“脏数据”的产生，确保数据的完整性和准确性。

1. 约束的类型概览

数据库提供了多种约束类型，来满足不同的“规则”需求。我们先来看一个总览，对它们有个初步印象：

类型	说明
`NOT NULL` (非空约束)	规定这一列的值不能是 `NULL`，必须得填点什么。
`DEFAULT` (默认约束)	如果我们插入数据时没有给这一列赋值，数据库会自动使用一个预设的默认值。
`UNIQUE` (唯一约束)	保证这一列中所有的值都是独一无二的，不能有重复。
`PRIMARY KEY` (主键约束)	它是 `NOT NULL` 和 `UNIQUE` 的结合体，是表中每一行数据的唯一身份标识。
`FOREIGN KEY` (外键约束)	用于建立两张表之间的关联关系，确保引用的数据是真实存在的。
`CHECK` 约束	一个更灵活的“检查员”，可以自定义各种复杂的规则来限制列中的值。

接下来，我们就一个个地把这些“安全锁”研究明白。

2. `NOT NULL` 非空约束

NOT NULL 是最简单也最常用的约束。它的作用就是强制某一列在插入或更新数据时，不能接受 NULL 值。

比如，我们创建一个学生表，但暂时不加任何约束：

DROP TABLE IF EXISTS student;
CREATE TABLE student(
  id BIGINT,
  name VARCHAR(20)
);

-- 尝试插入一条名字为 NULL 的记录
INSERT INTO student VALUES (1, NULL);

-- 查询结果，可以看到 name 是 NULL
SELECT * FROM student;

一条没有名字的学生记录，这显然是不合理的。所以，我们需要给 name 列加上非空约束，把它变成一个必填项。

DROP TABLE IF EXISTS student;
-- 在 name 列后面加上 NOT NULL 关键字
CREATE TABLE student (
  id BIGINT,
  name VARCHAR(20) NOT NULL
);

-- 再次尝试插入 NULL 值
INSERT INTO student VALUES (1, NULL);
-- 这次，数据库会直接报错，拒绝插入
-- ERROR 1048 (23000): Column 'name' cannot be null

-- 插入正常值就可以成功
INSERT INTO student VALUES (1, '张三');
SELECT * FROM student;

数据库会帮我们自动进行了校验，挡住了不合规的数据。我们可以通过 DESC 命令查看表结构，Null 这一列显示 NO，就代表该列不允许为空。

DESC student;

3. `DEFAULT` 默认值约束

DEFAULT 约束也很好理解，它提供了一个“默认选项”。当我们在插入新记录时，如果没有明确指定某一列的值，数据库就会自动使用这个默认值。

我们给学生表加上 age 列：

ALTER TABLE student ADD COLUMN age INT;

这时，如果我们只插入 id 和 name，age 列就会是 NULL。

INSERT INTO student(id, name) VALUES (1, '张三');
SELECT * FROM student;

假设大部分学生的年龄都是18岁，我们就可以把18设为默认值，简化插入操作。

DROP TABLE IF EXISTS student;
-- 在 age 列后使用 DEFAULT 关键字设置默认值
CREATE TABLE student (
  id BIGINT,
  name VARCHAR(20) NOT NULL,
  age INT DEFAULT 18
);

-- 插入时，不指定 age
INSERT INTO student(id, name) VALUES (2, '李四');

可以看到，李四的年龄被自动设为了18。

一个值得注意的点： 如果我们插入时明确地将 age 指定为 NULL，那么默认值约束就不会生效。用户的明确指定优先级更高。

INSERT INTO student(id, name, age) VALUES (3, '王五', NULL);
SELECT * FROM student;

4. `UNIQUE` 唯一约束

UNIQUE 约束确保了某列中的所有值都是独一无二、不能重复的。比如学生的学号、用户的身份证号，这些都应该是唯一的。

我们给学生表加上学号（sno）列，并为其设置唯一约束。

DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id BIGINT,
  name VARCHAR(20) NOT NULL,
  age INT DEFAULT 18,
  sno VARCHAR(10) UNIQUE  -- 为 sno 列添加唯一约束
);

-- 插入第一条记录，成功
INSERT INTO student(id, name, sno) VALUES (1, '张三', '100001');

-- 尝试插入第二条记录，使用相同的学号
INSERT INTO student(id, name, sno) VALUES (2, '李四', '100001');
-- 数据库报错，唯一约束生效
-- ERROR 1062 (23000): Duplicate entry '100001' for key 'student.sno'

一个有趣的特例： 在大多数数据库中，UNIQUE 约束的列是可以包含多个 NULL 值的。因为从逻辑上讲，NULL 并不等于 NULL，它代表的是“未知”，所以多个“未知”并不算重复。

查看表结构，Key 列显示 UNI 就表示该列有唯一约束。

DESC student;

5. `PRIMARY KEY` 主键约束

主键可以说是表中最重要的约束，它是每一行数据的唯一身份标识。我们可以把它想象成每个人的身份证号。

一个列如果被设置为主键，它将同时拥有两个属性：

NOT NULL：不能为空。
UNIQUE：必须唯一。

每个表最多只能有一个主键。这个主键可以由单个列构成，也可以由多个列共同构成（称为复合主键）。

DROP TABLE IF EXISTS student;
-- 直接使用 PRIMARY KEY 关键字定义主键
CREATE TABLE student (
  id BIGINT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  age INT DEFAULT 18,
  sno VARCHAR(10) UNIQUE
);

当我们把 id 设置为主键后，它就自动具备了非空和唯一的特性。如果我们尝试插入重复的 id，就会触发主键冲突。

INSERT INTO student(id, name, sno) VALUES (1, '张三', '100001');

-- 尝试插入 id 同样为 1 的记录
INSERT INTO student(id, name, sno) VALUES (1, '李四', '100002');
-- 报错：主键冲突
-- ERROR 1062 (23000): Duplicate entry '1' for key 'student.PRIMARY'

5.1 自增主键 `AUTO_INCREMENT`

在实际开发中，我们很少会手动去为每一条记录分配主键，这太麻烦了。通常，我们会把主键列设置为“自动增长”，让数据库来帮我们管理。

DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,  -- 设置 id 为自增主键
  name VARCHAR(20) NOT NULL,
  age INT DEFAULT 18,
  sno VARCHAR(10) UNIQUE
);

设置了 AUTO_INCREMENT 后，我们在插入数据时就可以完全不管 id 列，数据库会自动为我们生成一个唯一的、递增的值。

-- 插入时可以不写 id 列，或者将其值设为 NULL
INSERT INTO student(name, sno) VALUES ('张三', '100001');
INSERT INTO student(id, name, sno) VALUES (NULL, '李四', '100002');

SELECT * FROM student;

一个需要注意的现象： 如果某次插入因为其他原因失败了（比如学号重复），那么这次自增分配的主键值就会被“浪费”掉，不会被下一次插入使用。

-- 这次插入会因为学号 '100002' 重复而失败，但数据库内部已经为它准备好了 id=3
INSERT INTO student(name, sno) VALUES ('王五', '100002');
-- ERROR 1062 (23000): Duplicate entry '100002' for key 'student.sno'

-- 修正学号后，再次插入
INSERT INTO student(name, sno) VALUES ('王五', '100003');

-- 查询结果会发现，王五的 id 是 4，而不是 3
SELECT * FROM student;

另外，自增主键虽然是递增的，但不保证一定是连续的。我们也可以手动插入一个更大的值，后续的自增会从这个新的最大值开始。

#手动指定一个值
insert into student(id,name,sno) values (100,'赵六','100004');
select * from student;

# 下一次自增从主键的最大值开始
insert into student(name,sno) values ('钱七','100005');
select * from student;

在分布式系统中，为了避免不同服务器生成相同的主键，常常会为每台服务器预分配一个主键区间，这也是导致主键不连续的常见原因。
5.2 主键冲突时的处理策略

当我们插入的数据与现有的主键或唯一键冲突时，除了报错，MySQL还提供了两种优雅的处理方式：

ON DUPLICATE KEY UPDATE (存在则更新)

这个语法的意思是：尝试插入，如果发生主键或唯一键冲突，那就别报错了，改成执行更新操作。

-- 尝试插入 id=100 的记录，如果已存在，则更新它的 name 和 sno
INSERT INTO student(id, name, sno) VALUES (100, '赵六', '100100')
            ON DUPLICATE KEY UPDATE name = '赵六', sno = '100100';
-- Query OK, 2 rows affected... 这表示执行了“删除旧记录，插入新记录”的操作

REPLACE INTO (存在则替换)

这个语法更“暴力”一些：如果记录不存在，就插入；如果存在（根据主键或唯一键判断），就先删除旧的记录，再插入新的记录。

-- 如果 id=101 的记录存在，就删掉它，然后插入这条新的
REPLACE INTO student(id, name, sno) VALUES (101, '钱七', '100101');
-- Query OK, 2 rows affected...

-- 如果 id=102 的记录不存在，就直接插入
REPLACE INTO student(id, name, sno) VALUES (102, '吴八', '100102');
-- Query OK, 1 row affected...

5.3 复合主键

有时候，单个列不足以唯一标识一条记录，我们就需要用多个列组合起来作为主键，这就是复合主键。

DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id BIGINT,
  name VARCHAR(20),
  PRIMARY KEY (id, name)  -- 指定 id 和 name 共同组成主键
);

对于复合主键，只有当所有组成主键的列的值都完全相同时，才会被认为是主键冲突。

-- 插入成功
INSERT INTO student(id, name) VALUES (1, '张三');

-- 再次插入，因为 (1, '张三') 这个组合已经存在，所以冲突
INSERT INTO student(id, name) VALUES (1, '张三');
-- ERROR 1062 (23000): Duplicate entry '1-张三' for key 'student.PRIMARY'

-- 只改变其中一个列的值，就不算冲突，插入成功
INSERT INTO student(id, name) VALUES (2, '张三');

6. `FOREIGN KEY` 外键约束

外键是体现数据库“关系”的核心。它用于建立和加强两张表数据之间的联系，保证了数据的引用完整性。

我们用一个经典的“班级表”和“学生表”的例子来理解。一个班级可以有多个学生，一个学生只属于一个班级。在这里，班级表是“主表”，学生表是“从表”。

首先，创建主表 class：

DROP TABLE IF EXISTS class;
CREATE TABLE class (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL
);
-- 初始化一些班级数据
INSERT INTO class (name) VALUES ('java01'), ('java02'), ('java03'), ('C++01'), ('C++02');

然后，创建从表 student，并在其中定义一个外键，让它的 class_id 列引用 class 表的 id 列。

DROP TABLE IF EXISTS student;
CREATE TABLE student(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,  
  name VARCHAR(20) NOT NULL,
  age INT DEFAULT 18,
  class_id BIGINT,
  -- 定义外键：本表的 class_id 列，引用 class 表的 id 列
  FOREIGN KEY (class_id) REFERENCES class(id)
);

这个外键约束建立后，会产生以下效果：

插入限制：你不能在 student 表中插入一个 class_id 在 class 表中不存在的值。比如，你不能给学生分配一个不存在的班级。

-- 尝试插入一个 class_id 为 100 的学生，因为 class 表中没有 id=100 的班级，所以失败
INSERT INTO student(name, class_id) VALUES ('王五', 100);
-- ERROR 1452 (23000): Cannot add or update a child row...

删除限制：你不能从主表 class 中删除一个已经被从表 student 引用的记录。比如，如果 java01 班（假设id=1）里还有学生，你就不能直接删除这个班级。
```
-- 尝试删除 java01 班，因为有学生记录引用了它，所以失败
DELETE FROM class WHERE name = 'java01';
-- ERROR 1451 (23000): Cannot delete or update a parent row...
```
这条规则保证了不会出现“学生所属班级信息丢失”的情况。要想删除主表记录，必须先处理掉从表中所有依赖它的记录。

删表限制：不能直接删除被外键引用的主表。必须先删除从表，才能删除主表。

-- 直接删主表，失败
DROP TABLE class;
-- ERROR 3730 (HY000): Cannot drop table 'class' referenced by...

-- 正确的顺序
DROP TABLE student; -- 先删从表
DROP TABLE class;   -- 再删主表

外键就像一条牢固的锁链，将相关的表紧密地联系在一起，确保了数据之间逻辑关系的一致性和正确性。

7. `CHECK` 约束

CHECK 约束是一个通用的“校验器”，你可以用它来定义更复杂的、自定义的数据验证规则。

比如，我们要求学生的年龄必须大于等于16岁，性别只能是’男’或’女’。

DROP TABLE IF EXISTS student;
CREATE TABLE student(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  age INT DEFAULT 18,
  gender CHAR(1),
  -- 定义 CHECK 约束
  CHECK (age >= 16),
  CHECK (gender = '男' OR gender = '女')
);

-- 插入年龄小于16的记录，失败
INSERT INTO student(name, age, gender) VALUES ('张三', 15, '男');
-- ERROR 3819 (HY000): Check constraint 'student_chk_1' is violated.

-- 插入性别不合规的记录，失败
INSERT INTO student(name, age, gender) VALUES ('张三', 17, '1');
-- ERROR 3819 (HY000): Check constraint 'student_chk_2' is violated.

CHECK 约束甚至可以用于比较同一行中不同列之间的值。

CREATE TABLE t_check (
  c1 INT CHECK(c1 <> 0),
  c2 INT CHECK(c2 > 0),
  c3 INT,
  -- c3 必须大于等于 c2
  CHECK(c3 >= c2)
);

一点建议：
CHECK 约束虽然强大，但在 MySQL 8.0.16 版本之前并不被真正支持（语法能通过但不起作用），这导致了它的兼容性问题。在实际的项目中，更倾向于将这类复杂的业务逻辑校验放在应用程序层面（比如Java、Python代码中）来完成，而不是过度依赖数据库的 CHECK 约束。这样做能让业务规则更清晰，也更容易维护和迁移。

总结

好了，今天我们把数据库的几种核心约束都过了一遍。它们就像是数据库的“卫兵”，时刻守护着数据的准确性和一致性。

NOT NULL：保证数据不为空。
DEFAULT：提供省事的默认值。
UNIQUE：确保数据不重复。
PRIMARY KEY：每一行数据的唯一身份证。
FOREIGN KEY：连接不同表之间的关系纽带。
CHECK：自定义的超级校验器。

熟练掌握并合理使用这些约束，是每一个后端开发者的基本功。希望这篇笔记能帮助你更好地理解它们！

MYSQL：数据库约束

文章目录

MYSQL：数据库约束：为你的数据上把“安全锁”

1. 约束的类型概览

2. `NOT NULL` 非空约束

3. `DEFAULT` 默认值约束

4. `UNIQUE` 唯一约束

5. `PRIMARY KEY` 主键约束

5.1 自增主键 `AUTO_INCREMENT`

5.3 复合主键

6. `FOREIGN KEY` 外键约束

7. `CHECK` 约束

总结

网站公告

今日签到

热门文章

最新发布

MYSQL：数据库约束

文章目录

MYSQL：数据库约束：为你的数据上把“安全锁”

1. 约束的类型概览

2. NOT NULL 非空约束

3. DEFAULT 默认值约束

4. UNIQUE 唯一约束

5. PRIMARY KEY 主键约束

5.1 自增主键 AUTO_INCREMENT

5.3 复合主键

6. FOREIGN KEY 外键约束

7. CHECK 约束

总结

网站公告

今日签到

热门文章

最新发布

2. `NOT NULL` 非空约束

3. `DEFAULT` 默认值约束

4. `UNIQUE` 唯一约束

5. `PRIMARY KEY` 主键约束

5.1 自增主键 `AUTO_INCREMENT`

6. `FOREIGN KEY` 外键约束

7. `CHECK` 约束