SQL基础⑪ | 约束

0 序言

本文主要围绕数据库约束讲起，涵盖约束的基本概念、分类及各类约束（非空、唯一、主键、自增列、外键、CHECK、DEFAULT）的作用、特点、操作方法与示例。

通过学习，可掌握约束的定义逻辑、使用场景及实操技能，理解其在保证数据完整性中的关键作用。

1 约束（constraint）概述

1.1 为什么需要约束

从数据完整性来说，

• 数据完整性（Data Integrity）指数据的精确性（Accuracy）和可靠性（Reliability），目的是防止数据库中存在不符合语义规定的数据，避免错误信息导致无效操作或错误。

我们可以从四方面保证数据完整性：

• 实体完整性：如同一表中不能有两条完全相同无法区分的记录。
• 域完整性：如年龄范围0-120，性别范围“男/女”。
• 引用完整性：如员工所在部门需在部门表中存在。
• 用户自定义完整性：如用户名唯一、密码不能为空等。

1.2 什么是约束

约束是表级的强制规定，可在创建表时（通过CREATE TABLE语句）或表创建后（通过ALTER TABLE语句）设置。

1.3 约束的分类

1.3.1 按约束数据列的限制

• 单列约束：每个约束仅约束一列。
• 多列约束：每个约束可约束多列数据。

1.3.2 按约束的作用范围

• 列级约束：作用于单个列，跟在列定义后。
  • 支持的约束类型：语法上都支持，但外键无效果。
  • 是否可起约束名：不可以。
• 表级约束：作用于多个列，单独定义在所有列下方。
  • 支持的约束类型：默认和非空不支持，其他支持。
  • 是否可起约束名：可以（主键无效果）。

1.3.3 按约束的作用

• NOT NULL：非空约束，规定字段不能为空。
• UNIQUE：唯一约束，规定字段在表中唯一。
• PRIMARY KEY：主键约束（非空且唯一）。
• FOREIGN KEY：外键约束。
• CHECK：检查约束。
• DEFAULT：默认值约束。

1.4 查看表的约束

通过查询系统库信息查看：

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

这里就可以看到表约束详情。

2 非空约束（NOT NULL）

2.1 作用

限定某个字段/列的值不允许为空。
关键字为：NOT NULL

2.2 特点

• 默认情况下，所有类型的值都可为NULL（包括INT、FLOAT等）。
• 非空约束仅作用于单个列，一个表可有多列设非空约束。
• 空字符串’'≠NULL，0≠NULL。

2.3 添加非空约束

2.3.1 建表时添加

CREATE TABLE 表名称(
  字段名 数据类型,
  字段名 数据类型 NOT NULL,
  字段名 数据类型 NOT NULL
);

-- 示例
CREATE TABLE emp(
  NAME VARCHAR(20) NOT NULL,
  sex CHAR NULL
);

CREATE TABLE student(
  sid INT,
  sname VARCHAR(20) NOT NULL,
  tel CHAR(11),
  cardid CHAR(18) NOT NULL
);

• 插入数据示例：

-- 成功（所有非空字段均赋值）
INSERT INTO student VALUES(1,'张三','13710011002','110222198912032545');
-- 失败（cardid为NULL，违反非空约束）
INSERT INTO student VALUES(2,'李四','13710011002',NULL);
-- 成功（tel允许为空）
INSERT INTO student VALUES(2,'李四',NULL,'110222198912032546');
-- 失败（sname为NULL，违反非空约束）
INSERT INTO student VALUES(3,NULL,NULL,'110222198912032547');

可以自己动手试一下，

规则出错会弹出错误信息，

但只要满足建表是设定好的约束条件即可。

2.3.2 建表后添加

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL;

-- 示例
ALTER TABLE emp MODIFY sex VARCHAR(30) NOT NULL;
ALTER TABLE student MODIFY sname VARCHAR(20) NOT NULL;

这样就可以在已有的表格进行约束添加。

2.4 删除非空约束

-- 方式1：显式指定NULL
ALTER TABLE 表名称 MODIFY 字段名 数据类型 NULL;
-- 方式2：不指定NOT NULL（默认允许为空）
ALTER TABLE 表名称 MODIFY 字段名 数据类型;

-- 示例
ALTER TABLE emp MODIFY sex VARCHAR(30) NULL;
ALTER TABLE emp MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

3 唯一约束（UNIQUE）

限制某个字段/列的值在整个表中唯一。

这里核心就是唯一，关键字为：UNIQUE

3.1 特点

• 一个表可有多个唯一约束。
• 可约束单个列或多列组合（复合唯一）。
• 允许列值为空（可多个NULL）。
• 未命名时，默认与列名相同（单列）或与组合列中第一个列名相同（多列）。
• MySQL会为唯一约束列自动创建唯一索引。

3.2 添加唯一约束

3.2.1 建表时添加

-- 列级约束方式
CREATE TABLE 表名称(
  字段名 数据类型,
  字段名 数据类型 UNIQUE,
  字段名 数据类型 UNIQUE KEY
);

-- 表级约束方式
CREATE TABLE 表名称(
  字段名 数据类型,
  字段名 数据类型,
  字段名 数据类型,
  CONSTRAINT 约束名 UNIQUE KEY(字段名)
);

-- 示例
CREATE TABLE student(
  sname VARCHAR(20),
  tel CHAR(11) UNIQUE,
  cardid CHAR(18) UNIQUE KEY
);

CREATE TABLE t_course(
  cid INT UNIQUE,
  cname VARCHAR(100) UNIQUE,
  description VARCHAR(200)
);

-- 复合唯一（用户名和密码组合唯一）
CREATE TABLE USER(
  id INT NOT NULL,
  NAME VARCHAR(25),
  PASSWORD VARCHAR(16),
  C