06 表的约束

表的约束

1. 什么是表的约束？

表的约束就是 限制字段中能存什么数据、不能存什么数据 的规则，目的：防止脏数据进入数据库。举例理解：

  • 不允许“性别”列里填手机号。
  • 不允许“年龄”列为空或写成负数。
  • 不允许两个人用同一个账号。
  • ……

2. 空属性（NULL / NOT NULL）

1. 空属性介绍

属性含义特点
NULL可以不填默认状态
NOT NULL必须有值插入时没写就会报错

注意：

  • 空值 NULL 不等于空字符串 ''，也不等于 0。
  • NULL 参与运算的结果仍是 NULL，比如：NULL + 1 = NULL。
  • 实际开发中：能不为空的字段尽量设置 NOT NULL。

2. 语法格式

1
列名 数据类型 [NULL | NOT NULL]

3. 基本使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 创建表t1，字段约束说明：name：非空（not null），必须提供值；age和room：允许为null（默认可省略null关键字）
create table t1(
    name varchar(20) not null,
    age varchar(20) null,
    room varchar(20));

# 查看表结构：Null列显示NO表示非空（name），YES表示允许为空（age、room）；Default均为NULL
desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | NO   |     | NULL    |       |  # 非空字段，必须赋值
| age   | varchar(20) | YES  |     | NULL    |       |  # 可空字段，可省略赋值
| room  | varchar(20) | YES  |     | NULL    |       |  # 可空字段，可省略赋值
+-------+-------------+------+-----+---------+-------+

# 插入时未给非空字段name赋值 → 报错（非空字段必须提供值）
insert into t1(age) values(18);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

# 只给非空字段name赋值，可空字段age、room省略 → 成功（省略字段自动为NULL）
insert into t1(name) values('张三');

# 未给非空字段name赋值，只给可空字段赋值 → 报错
insert into t1(age,room) values(18,520);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

# 给非空字段name和可空字段room赋值，age省略 → 成功
insert into t1(name,room) values('张三',520);

3. 默认值（DEFAULT）

1. 默认值介绍

DEFAULT 表示字段在 没写值时自动填默认值。

2. 语法格式

1
2
列名 数据类型 [DEFAULT 默认值]
age INT DEFAULT 18		# 向表中插入时没写 age，会自动补 18。如果写了值，则按自己写的为准。

常见用法：

  • DEFAULT 0 表示默认值为 0。
  • DEFAULT CURRENT_TIMESTAMP 表示默认当前时间。

3. 基本使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 创建表t2，定义4个字段并为部分字段设置默认值：
# id：无默认值（需显式赋值）；age：默认值18；status：默认值'未激活'；create_time：默认当前时间
create table t2(
    id int,
    age int default 18,  # 整数类型，未赋值时默认填充18
    status VARCHAR(10) DEFAULT '未激活',  # 字符串类型，未赋值时默认填充'未激活'
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP);  # 日期时间类型，未赋值时默认当前系统时间

# 仅给id赋值，其他字段使用默认值：age→18（默认）、status→'未激活'（默认）、create_time→插入时的当前时间
insert into t2(id) values(100);

# 给所有字段赋值，其中create_time用default显式调用默认值（当前时间）：
# id=200、age=50（覆盖默认18）、status='已激活'（覆盖默认'未激活'）、create_time→当前时间
insert into t2 values(200,50,'已激活',default);

# 查询结果：第一条记录：未赋值字段均显示默认值；第二条记录：显式赋值字段覆盖默认，create_time仍用当前时间
select * from t2;
+------+------+-----------+---------------------+
| id   | age  | status    | create_time         |
+------+------+-----------+---------------------+
|  100 |   18 | 未激活    | 2025-10-02 19:32:48 |  # age=18、status=未激活（默认），create_time为第一条插入时间
|  200 |   50 | 已激活    | 2025-10-02 19:34:07 |  # age=50、status=已激活（覆盖默认），create_time为第二条插入时间
+------+------+-----------+---------------------+

4. 同时设置 not null 和 default

一旦给某一字段设置了默认值，那么该字段将不会出现空值，因为就算插入数据时没有指明该字段的值，也会使用该字段的默认值进行填充。而给某一字段设置 not null 属性的目的是约束该字段不能为空，因此一个字段设置了 default 属性后，再设置 not null 属性就没有意义了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
# 1. 创建表t3：故意只给age设default、不给name设default，用于对比
create table t3(
    name varchar(20) not null,  # 仅设not null，无default
    age int not null default 18  # 同时设not null + default
);

# 查看表结构：确认约束生效
desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | NO   |     | NULL    |       |  # 仅not null，Default为NULL
| age   | int         | NO   |     | 18      |       |  # not null+default，Default为18
+-------+-------------+------+-----+---------+-------+

# 2. 实验1：插入时不指定name（仅not null无default）→ 报错
# 原因：name是not null但无默认值，未赋值时无法填充，触发非空约束
insert into t3(age) values(20);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

# 3. 实验2：插入时不指定age（not null+default）→ 成功
# 原因：age虽为not null，但有default 18，未赋值时自动用默认值填充，满足非空约束
insert into t3(name) values('张三');

# 4. 实验3：显式给age赋值（覆盖默认值）→ 成功，验证：default不影响显式赋值，仅在“未赋值”时生效
insert into t3(name, age) values('李四', 22);

# 5. 查看结果：确认default的填充效果
select * from t3;
+------+-----+
| name | age |
+------+-----+
| 张三 |  18 |  # 未给age赋值，自动填充default 18（满足not null）
| 李四 |  22 |  # 显式赋值22，覆盖默认值
+------+-----+

# 6. 反证实验：若age只设default、不设not null（模拟“认为default足够”的场景）
create table t4(
    age int default 18  # 仅设default，无not null
);
# 插入时主动给age赋null → 成功（此时出现空值，违背“不允许空”的初衷）
insert into t4(age) values(null);

# 查看结果：出现了空值，证明仅靠default无法约束“主动插入null”
select * from t4;
+------+
| age  |
+------+
| NULL |  # 主动插入null时，default失效
+------+

但也会用于强制字段非空 + 自动填充（即确保开发者不能主动插入 null，只能自动或手动写具体值）：

1
2
3
create table users(
    status tinyint not null default 0 comment '0=未激活,1=已激活'
);

4. 列描述

1. 列描述介绍

给表或字段添加说明文字，用来 描述字段含义，不会影响功能，只是为了 方便阅读与维护。

2. 语法格式

1
2
3
4
5
列名 数据类型 [约束] COMMENT '描述信息';
# 或者在创建表时添加表注释：
CREATE TABLE 表名 (
    ...
) COMMENT='表的说明';

3. 基本使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 创建表t3，为每个字段添加comment（字段注释，说明字段含义）
create table t3(
    id int comment '这是id编号',  # 给id字段加注释
    name varchar(20) comment '这是姓名',  # 给name字段加注释
    age int default 18 comment '这是年龄');  # 给age字段加注释（同时有默认值）

# 查看t3的完整创建语句，确认注释是否生效
show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int DEFAULT NULL COMMENT '这是id编号',  # 字段注释显示在字段定义后
  `name` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '这是姓名',  # 字段注释生效
  `age` int DEFAULT '18' COMMENT '这是年龄'  # 字段注释+默认值同时生效
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 创建表t4，在表定义末尾添加comment（表级注释，说明表的用途）
create table t4(
    id int,
    name varchar(20)
    ) comment='学生信息表';  # 给表本身加注释

# 查看t4的表状态信息，确认表注释是否生效
show table status like 't4'\G
*************************** 1. row ***************************
           Name: t4
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2025-10-02 19:51:30
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 学生信息表  # 表注释显示在Comment字段中

5. ZEROFILL（补零属性）

1. zerofill 介绍

数字类型前自动补零，主要用于 统一显示位数，常用于编号、ID、订单号等字段。

2. 语法格式

1
列名 数据类型(ZEROFILL)

特性说明:

  1. 自动加上 UNSIGNED（无符号）属性，即不允许负数！
  2. 只影响 显示，不会改变实际存储的数值。
  3. 括号中的数字是 显示宽度，不是有效位数。
  4. 在 MySQL 8.0.17 之后，ZEROFILL 已 不推荐使用（废弃特性），建议用 LPAD() 函数代替。

3. 基本使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 创建表t5，id字段加zerofill（数字前补0）
create table t5(id int zerofill);

# 插入1，显示时补0到int默认长度（10位）
insert into t5 values(1);

# 插入12，补0到10位
insert into t5 values(12);

# 插入123，补0到10位
insert into t5 values(123);

# 插入负数，zerofill隐含unsigned（无符号），负数不允许
insert into t5 values(-123);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

# 插入0，补0到10位
insert into t5 values(0);

# 查询结果：数字不足10位的，前面补0凑够长度
select *from t5;
+------------+
| id         |
+------------+
| 0000000001 |
| 0000000012 |
| 0000000123 |
| 0000000000 |
+------------+

6. 主键（PRIMARY KEY）

1. 主键介绍

主键用于 唯一标识一行数据，就像身份证号一样，表中每行数据都要有一个主键值。主键列的值不能重复，也不能为空（NOT NULL）。

2. 语法格式

1
2
3
列名 数据类型 PRIMARY KEY
# 或者单独写在最后：
PRIMARY KEY (列名)
特性说明
不能重复每行唯一
不能为空必须有值
每张表只能有一个主键但可由多个列组成（联合主键）
通常与 AUTO_INCREMENT 一起用自动编号更方便

3. 基本使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 创建表t6，id设为主键（primary key，唯一且非空）
create table t6(
    id int primary key,
    name varchar(20));

# 正确插入：id=108（主键），符合格式
insert into t6 values(108,'minbit');

# 插入重复主键：id=108已存在，主键必须唯一，报错
insert into t6 values(108,'小米里的大麦');
ERROR 1062 (23000): 主键值108重复

# 插入新主键：id=100（新值），成功
insert into t6 values(100,'小米里的大麦');

添加联合（复合）主键（多个字段一起作为主键）：

  • 多个字段共同组成主键。
  • 常用于需要联合唯一标识一行数据的场景，比如学生 + 课程。
1
2
3
4
5
6
7
8
9
ALTER TABLE 表名 ADD PRIMARY KEY (列名1, 列名2);

# 同一个学生在同一门课中只能有一条成绩记录
CREATE TABLE score (
    student_id INT,
    course_id INT,
    score INT,
    PRIMARY KEY (student_id, course_id)   # 联合/复合主键
);

（表创建后）添加主键：

  • 给表中某个字段设置主键约束。
  • 如果字段中已有重复值或空值，命令会失败（因为主键必须唯一且非空）。
1
2
ALTER TABLE 表名 ADD PRIMARY KEY (列名);
alter table students ADD PRIMARY KEY (id);

删除主键：

  • 删除表中的主键约束。
  • 注意：如果主键列是 AUTO_INCREMENT，删除主键后自增属性会失效。
1
2
ALTER TABLE 表名 DROP PRIMARY KEY;
alter table students DROP PRIMARY KEY;
操作通用语法说明
添加主键ALTER TABLE 表名 ADD PRIMARY KEY (列名);为现有字段设置主键
删除主键ALTER TABLE 表名 DROP PRIMARY KEY;删除主键约束
联合主键ALTER TABLE 表名 ADD PRIMARY KEY (列1, 列2);多列联合唯一
修改主键先 DROP 再 ADD重新指定主键

7. 自增长（AUTO_INCREMENT）

1. 自增长介绍

AUTO_INCREMENT 表示 自动编号，插入新数据时自动加 1，最常与主键一起使用。

2. 语法格式

1
2
3
列名 数据类型 AUTO_INCREMENT
# 一般配合主键：
id INT PRIMARY KEY AUTO_INCREMENT

注意事项：

  1. 一张表只能有 一个自增字段。
  2. 自增字段必须是整数类型。
  3. 一般与主键一起用。
  4. 删除数据不会回退编号（除非手动重置）。

如果删除最后一行，再插入新行，id 不会复用旧编号，想手动恢复编号，可执行：alter table 表名 auto_increment = 新编号;。如果表被 TRUNCATE TABLE 清空（比 delete 快），自增编号会被重置为 1。

3. 基本使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
# 创建表t7，id设为主键且自动增长（auto_increment）
create table t7(
    id int primary key auto_increment,  # id自动生成唯一值，无需手动插入
    name varchar(20) );

# 插入数据时，只需指定name，id会自动增长（插入时没写 id，MySQL 自动编号）
insert into t7 (name) values ('张三');  # id自动为1
insert into t7 (name) values ('李四');  # id自动为2
insert into t7 (name) values ('王五');  # id自动为3

# 查询结果：id按插入顺序自动生成1、2、3
select * from t7;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
+----+--------+

# 删除name为"李四"的记录（id=2）
delete from t7 where name = "李四";

# 再次查询：id=2的记录被删除，后续插入的id不会填补空缺
select * from t7;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  3 | 王五   |  # id仍为3，自动增长不会回退
+----+--------+

# 修改t7表的自增起始值为1000（不影响已有数据）
alter table t7 auto_increment = 1000;

# 查看表数据：已有id不变（1、3）
select * from t7;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  3 | 王五   |
+----+--------+

# 插入新数据：新id从1000开始（按修改后的自增值）
insert into t7 (name) values ('老李');

# 新记录id为1000，符合设置的起始值
select * from t7;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    3 | 王五   |
| 1000 | 老李   |
+------+--------+

# 查看表状态：Auto_increment显示下一个自增值为1001（当前最大id+1）
show table status like 't7'\G
*************************** 1. row ***************************
           Name: t7
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1001	# 下一次插入将使用1001
    Create_time: 2025-10-02 20:33:15
    Update_time: 2025-10-02 20:34:12
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment:

# 尝试将自增起始值改回1（但受已有最大id影响）
alter table t7 auto_increment=1;

# 已有数据id仍不变
select * from t7;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    3 | 王五   |
| 1000 | 老李   |
+------+--------+

# 插入新数据：自增值不会小于已有最大id（1000），故新id为1001
insert into t7 (name) values ('老王');

# 新记录id为1001（下一个自增值）
select * from t7;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    3 | 王五   |
| 1000 | 老李   |
| 1001 | 老王   |
+------+--------+

8. 唯一键（UNIQUE）

1. 唯一键介绍

UNIQUE KEY 用于保证字段值不重复，与主键类似，但区别是：唯一键 可以为空（NULL）、一张表可以有多个唯一键。

2. 语法格式

1
2
3
列名 数据类型 UNIQUE
# 或单独添加：
ALTER TABLE 表名 ADD UNIQUE (列名);
操作通用语法示例
删除唯一键ALTER TABLE 表名 DROP INDEX 索引名;ALTER TABLE t8 DROP INDEX user;
添加唯一键ALTER TABLE 表名 ADD UNIQUE (列名);ALTER TABLE t8 ADD UNIQUE (user);
添加唯一键（自定义名）ALTER TABLE 表名 ADD CONSTRAINT 索引名 UNIQUE (列名);ALTER TABLE t8 ADD CONSTRAINT un_user UNIQUE (user);

3. 基本使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# 建t8表，给user和email加unique（唯一约束）：两字段值都不能重复
create table t8(
    user varchar(20) unique,
    email varchar(20) unique);

# 正确插入：user=minbit、email=abc.com（均为新值，符合唯一约束）
insert into t8 values('minbit','abc.com');

# 插入重复值：user和email都和已有记录重复，触发user字段唯一约束报错
insert into t8 values('minbit','abc.com');
ERROR 1062 (23000): Duplicate entry 'minbit' for key 't8.user'

# 插入重复user：仅user重复，仍触发唯一约束报错
insert into t8 values('minbit','123.com');
ERROR 1062 (23000): Duplicate entry 'minbit' for key 't8.user'

# 插入新user和新email：均为新值，成功
insert into t8 values('tim','123.com');

# 插入重复email：email=123.com已存在，触发email字段唯一约束报错
insert into t8 values('sam','123.com');
ERROR 1062 (23000): Duplicate entry '123.com' for key 't8.email'

# 插入null值：unique约束允许null（null不视为重复），成功
insert into t8 values(null,null);

# 再插入null值：仍允许（null不比较重复），成功
insert into t8 values(null,null);

# 查询结果：有重复null，但非null值均唯一
select * from t8;
+--------+---------+
| user   | email   |
+--------+---------+
| minbit | abc.com |  # 首次插入的非null记录
| tim    | 123.com |  # 第二次插入的非null记录
| NULL   | NULL    |  # 允许null
| NULL   | NULL    |  # 允许重复null
+--------+---------+

如果多列组成唯一组合，可写：alter table users add unique (user, email);，表示 user + email 组合唯一，但单独字段可重复。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
# 查看t8表的索引信息（\G垂直显示更清晰）
mysql> show index from t8\G
*************************** 1. row ***************************
        Table: t8
   Non_unique: 0	 # 唯一索引（对应email的unique约束）
     Key_name: user  # 索引名（和字段名一致）
 Seq_in_index: 1  # 字段在索引中的位置（单字段索引为1）
  Column_name: user  # 索引对应的字段
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: t8
   Non_unique: 0  # 唯一索引（对应email的unique约束）
     Key_name: email  # 索引名（和字段名一致）
 Seq_in_index: 1  # 单字段索引，位置1
  Column_name: email 
    Collation: A
  Cardinality: 3
     Sub_part: NULL 
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES 
   Expression: NULL

# 删除t8表中名为user的索引（即删除user字段的unique约束）
mysql> alter table t8 drop index user;

# 删除t8表中名为email的索引（即删除email字段的unique约束）
mysql> alter table t8 drop index email;

# 再次查看t8表索引：已无索引（unique约束已删除）
mysql> show index from t8\G
Empty set (0.00 sec)

# 给t8表的user字段重新添加unique约束（自动创建同名索引）
mysql> alter table t8 add unique (user);

# 给t8表的email字段重新添加unique约束（自动创建同名索引）
mysql> ALTER TABLE t8 ADD UNIQUE (email);

# 查看索引：user和email的唯一索引已恢复
mysql> show index from t8\G
*************************** 1. row ***************************
        Table: t8
   Non_unique: 0
     Key_name: user  # 索引名（和字段名一致）
 Seq_in_index: 1  # 字段在索引中的位置（单字段索引为1）
  Column_name: user  # 索引对应的字段
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: t8 
   Non_unique: 0  # 唯一索引（对应email的unique约束）
     Key_name: email  # 索引名（和字段名一致）
 Seq_in_index: 1  # 单字段索引，位置1
  Column_name: email 
    Collation: A
  Cardinality: 3
     Sub_part: NULL 
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES 
   Expression: NULL

9. 外键（FOREIGN KEY）

1. 外键介绍

外键用于 建立两张表之间的关联关系，保证数据一致性，通常表示“一张表的数据依赖另一张表”。

例如：

  • 学生表（students）依赖班级表（classes）的班级号；
  • 删除班级时，相关学生数据不能孤立。

2. 语法格式

1
2
3
4
[CONSTRAINT 外键名]
FOREIGN KEY (当前表字段)
REFERENCES 被关联表(被关联字段)
[ON DELETE 操作] [ON UPDATE 操作];

外键级联规则说明：

操作含义
ON DELETE CASCADE删除父表记录 → 自动删除子表对应记录
ON UPDATE CASCADE更新父表主键 → 自动更新子表对应外键
ON DELETE SET NULL删除父表记录 → 子表字段设为 NULL
ON DELETE RESTRICT删除父表记录 → 若子表存在关联数据则拒绝删除（默认）

外键注意事项：

  1. 外键字段必须与被引用字段类型相同。
  2. 外键列和被引用列必须使用相同的存储引擎（InnoDB）。
  3. 被引用的列必须是主键或唯一键。
  4. 外键会影响删除、更新操作性能，不建议在高并发系统中过度使用。

3. 基本使用

创建时定义外键：

1
2
3
4
5
6
7
8
9
10
11
12
# 含义：students.class_id 必须对应 classes.id 中已有的值，不能插入不存在的班级号
create table classes (
    id int primary key,
    name varchar(50)
);

create table students (
    id int primary key,
    name varchar(50),
    class_id int,
    foreign key (class_id) references classes(id)   # 建立外键关联
);

表创建后添加外键：

1
2
3
alter table students
add constraint fk_class
foreign key (class_id) references classes(id);

删除外键：

1
alter table students drop foreign key fk_class;

设置级联操作（ON DELETE / ON UPDATE）：

1
2
3
4
5
6
7
8
9
create table students (
    id int primary key,
    name varchar(50),
    class_id int,
    foreign key (class_id)
    references classes(id)
    on delete cascade       # 删除班级时自动删除相关学生
    on update cascade       # 修改班级id时自动同步学生表
);
特性主键（PRIMARY KEY）唯一键（UNIQUE KEY）外键（FOREIGN KEY）
是否唯一✅ 是✅ 是❌ 否（用于关联）
是否可空❌ 否✅ 可为空✅ 可为空
可否多个❌ 只能一个✅ 可多个✅ 可多个
用途唯一标识行保证字段唯一建立表间关系
是否自动索引✅ 自动创建✅ 自动创建❌ 不自动创建
依赖其他表❌ 否❌ 否✅ 是

10. 小结

1
2
3
4
5
6
7
8
9
10
11
12
13
create table t9 (
    id int primary key auto_increment comment '学生编号，主键且自动增长',  # 主键 + 自增 + 注释
    name varchar(30) not null comment '学生姓名，不能为空',               # 非空约束
    gender char(1) default '男' comment '性别，默认值为男',              # 默认值
    age tinyint unsigned default 18 comment '年龄，默认18岁',            # 默认值 + 无符号
    student_no char(10) unique comment '学号，唯一',                     # 唯一约束
    class_id int comment '班级编号，对应班级表id',                       # 外键列
    score decimal(5,2) zerofill comment '成绩，补零显示，最大999.99',    # 补零效果（仅显示）
    enroll_date date default (current_date) comment '入学日期，默认当天', # 默认值为当前日期
    foreign key (class_id) references classes(id)                        # 外键约束
        on delete set null                                                # 删除班级后学生class_id置空
        on update cascade                                                 # 更新班级id后同步更新
) comment='学生信息表';
约束类型作用是否可为空是否唯一可否多个常见定义方式常见用途与说明
NOT NULL限制字段不能为空❌ 否❌ 否✅ 可多个age INT NOT NULL防止出现空值，保证字段必须有内容
DEFAULT指定默认值✅ 可为空（若未定义）❌ 否✅ 可多个age INT DEFAULT 18插入数据未指定该字段时自动填入默认值
COMMENT添加列或表的注释说明✅ 可为空❌ 否✅ 可多个name VARCHAR(20) COMMENT '学生姓名'提示说明作用，不影响功能
ZEROFILL数字显示前自动补零✅ 可为空❌ 否✅ 可多个id INT(5) ZEROFILL仅影响显示效果，新版已弃用（推荐 LPAD()）
PRIMARY KEY主键，唯一标识表中每行数据❌ 否✅ 是❌ 仅一个id INT PRIMARY KEY / ALTER TABLE ... ADD PRIMARY KEY(id)强制唯一且非空，可配合 AUTO_INCREMENT 使用
AUTO_INCREMENT自动增长字段❌ 否✅ 是❌ 仅一个id INT PRIMARY KEY AUTO_INCREMENT通常与主键一起使用，自动编号
UNIQUE KEY唯一约束，防止重复✅ 可为空✅ 是✅ 可多个email VARCHAR(50) UNIQUE / ALTER TABLE ... ADD UNIQUE(email)保证字段值唯一，可有多个 NULL
FOREIGN KEY外键约束，建立表与表之间的关系✅ 可为空❌ 否✅ 可多个FOREIGN KEY(class_id) REFERENCES classes(id)保证数据一致性，可配合 ON DELETE、ON UPDATE 实现级联操作