MySQL约束概述

约束作用

在 MySQL 中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。
例如，在数据表中存放年龄的值时，如果存入 200、300 这些无效的值就毫无意义了。因此，使用约束来限定表中的数据范围是很有必要的。

约束类型

🍧 主键约束

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。
主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。

🌴 外键约束

外键约束经常和主键约束一起使用，用来确保数据的一致性。
例如，一个水果摊，只有苹果、桃子、李子、西瓜 4 种水果，那么，你来到水果摊要买水果只能选择苹果、桃子、李子和西瓜，不能购买其它的水果。

🍊唯一约束

唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的，虽然只能有一个空值。
例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

🌱 检查约束

检查约束是用来检查数据表中，字段值是否有效的一个手段。
例如，学生信息表中的年龄字段是没有负数的，并且数值也是有限制的。如果是大学生，年龄一般应该在 18~30 岁之间。在设置字段的检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。

❄️ 非空约束

非空约束用来约束表中的字段不能为空。
例如，在学生信息表中，如果不添加学生姓名，那么这条记录是没有用的。

🍋 默认值约束

默认值约束用来约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。
例如，在注册学生信息时，如果不输入学生的性别，那么会默认设置一个性别或者输入一个“未知”。

默认值约束通常用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。

外键策略

外键策略分为
策略1：no action 不允许操作。
策略2：cascade级联操作；操作主表的时候也影响着从表的外键信息。
策略3：set null 置空操作。

不允许操作（no action）

  1. 先把班级4的学生对应的班级改为null
  2. 例：
  3. 1 |  update t_student set classno=null where classno=4;
  5. 然后再删除班级4
  6. 例：
  7. 2 |  delete from t_class where cno = 4

级联操作（cascade）

  1. 先删除添加之前外键约束
  2. 例：
  3. 1 |  alter table t_student drop foreign key fk_stu_class
  5. 重新添加外键约束
  6. 例：
  7. 2 |  alter table t_student add constraint fk_stu_class foreign key (classno) references t_class (cno) on update cascade on delete cascade;
  9. 试试更新
  10. 例：
  11. 3 |  update t_class set cno=5 where cno=3;
  13. 试试删除
  14. 例：
  15. 4 |  delete from t_class where cno=5;

置空操作（set null）

  1. 先删除添加之前外键约束
  2. 例：
  3. 1 |  alter table t_student drop foreign key fk_stu_class
  5. 重新添加外键约束
  6. 例：
  7. 2 |  alter table t_student add constraint fk_stu_class foreign key (classno) references t_class (cno) on update set null on delete set null;
  9. 试试更新
  10. 例：
  11. 3 |  update t_class set cno=8 where cno=1;

外键约束

什么是外键约束？
外键约束（**FORELGN KEY,缩写FK**）是用来实现数据库表的参照**完整性的**。外键约束可以使用两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。

外键约束指：
外键是指表中某个字段的值依赖与另一个表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为**父表或者主表**，设置外键约束的表称为**子表或者从表。**

举例：
如果想要表示学生和班级的关系，首先要有学生表和班级表，然后学生表中有个字段为stu_clazz(该字段表示学生所在的班级），而该字段的取值范围由班级表中的主键cla_no字段（该字段表示班级编号）的取值决定，学生为子表，且stu_clazz字段是学生表的外键。通过stu_clazz字段就建立了学生表和班级表的关系。

父表

  1. CREATE TABLE t_class
  2.      (
  3.      cno INT(7) PRIMARY key auto_increment,    
  4.      cname VARCHAR(10) not null,  
  5.      room char(4)
  6.      );



子表

**注意外键约束只有表级约束，没有列级约束**

  1. 子表1：
  2. 1 | CREATE TABLE t_student (
  3.     sno int (5) PRIMARY key auto_increment,
  4.     sname VARCHAR(5) not NULL,
  5.     classno int(7)
  6.     constraint fk_stu_class foreign key (classno) references t_class (cno)
  7.     );
  9. 子表2：
  10. 2 | CREATE TABLE t_student (
  11.     sno int (5) PRIMARY key auto_increment,
  12.     sname VARCHAR(5) not NULL,
  13.     classno int(7)
  14.     );
  15. 在创建表以后添加外键约束
  16. 2 | alter table t_student add constraint fk_stu_class foreign key (classno) references t_class (cno);

验证外键：
当班级表（父表）里的班级编号没有2时，添加学生表（子表）的学生编号为2时，是否添加学生信息成功。

  1. 例子：
  2. 1 | INSERT into t_student VALUES (null,"李四",1),(null,"张三",1),(null,"张是",2);
  4. 提示：Cannot add or update a child row: a foreign key constraint fails (`t_table`.`t_student`,
  5.       CONSTRAINT`fk_stu_class` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
  6.       无法添加或更新子行:外键约束失败

当学生表（子表）里有学生编号为3时，删除班级表（父表）的班级编号为3的是否成功。

  1. 例子：
  2. 1 |  delete from t_class where cno=3;
  4. 提示： Cannot delete or update a parent row: a foreign key constraint fails (`t_table`.
  5.       `t_student`, CONSTRAINT `fk_stu_class` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
  6.       不能删除或更新父行:外键约束失败(

非外键约束

创建表结构

  1. CREATE TABLE 表名 (
  2. 字段1 数据类型(长度) 非外键约束,
  3. 字段2 数据类型(长度) 非外键约束,
  4. .....
  5. );
  1. CREATE TABLE t_student (
  2. sno int (5) PRIMARY key auto_increment,
  3. sname VARCHAR(5) not NULL,
  4. sex CHAR(1) DEFAULT '男' CHECK(sex = '男'||sex = '女'),
  5. age int(3) CHECK(age>=20 AND age<=50),
  6. enterdate date,
  7. classname VARCHAR(10),
  8. email VARCHAR(16) UNIQUE
  9. );

主键约束

主键约束 PRIMARY KRY的作用：
约束表中的某个字段可以唯一标识一条记录，因此，使用主键约束可以快速查找表中记录。就像人的身份证.学生的学号等等，设置主键的字段取值不能重复（唯一），也不能为空（非空），否则无法唯一标识一条记录。

  1. 验证设置主键的学号取值不能重复？--sno int (5) PRIMARY key 
  3. 示例：
  5. 1 |  INSERT into t_student VALUES (1005,"小李","男",22,"2020/2/1","测试一班","xiaoli@qq.com");
  7. 1 |  INSERT into t_student VALUES (1005,"小刚","男",26,"2020/2/8","测试一班","xiaol@qq.com");
  9. 提示：提示：Duplicate entry '1001' for key 't_student.PRIMARY'提示主键重复（已有sno=1005）
  12. 验证设置主键的学号取值不能为空？--sno int (5) PRIMARY key 
  14. 当验证主键时没有设定值，或者用null，delauft都可以完成主键自增效果。
  16. 示例：
  18. 2 |  iNSERT into t_student VALUES (null,"李四","男",20);
  20. 2 |  iNSERT into t_student VALUES (default,"李四","男",20,"2020/2/4","测试一班","145@qq.com");

字段值自动增加约束

自增约束AUTO_INCREMENT
可以使表中某个字段的值自动增加，一张表中只能有一个自增字段，并且该字段必须定义了约束（该主键可以是主键约束，唯一约束以及外键约束），如果自增字段没有定义约束，数据库则会提示“incorrect table definition ；there can be only one auto column and it must be defind as a key”错误。

验证自增字段如果没有定义约束，SQL展示如下图：

  1. 先创建一张表：没有定义主键约束 唯一约束和外键约束
  3.  CREATE TABLE t_student (
  4.      sno int (5)  auto_increment
  5.      );
  7. 提示报错：
  8. Incorrect table definition; there can be only one auto column and it must be defined as a key
  9. 不正确的表定义; 只能有一个自动列，它必须定义为一个键

验证字段的值是否自动增加，SQL展示如下图：

  1. 验证学号的取值是否会自动自增
  3. sno int (5) PRIMARY key auto_increment  
  5. 示例：
  6. --先添加一条数据--
  7. 1 | INSERT into t_student VALUES (1001,"李四","男",20);
  9. --验证学号是否自动增加--
  10. 2 | INSERT into t_student VALUES (null,"李三","男",20);
  11. 2 | INSERT into t_student(sname,sex,age) VALUES ("李三","男",20);

返回的结果：学号已自动增加
MySQL约束 - 图1


非空约束

非空约束NOT NULL
规定了一张表中指定的某个字段的值不能为空（null）。设置了非空约束的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。无论是单个字段还是多个字段非空约束的添加只能使用列级约束（非空约束无表级约束）。

  1. 验证学生名字的取值不能为null。 --sname VARCHAR(5) not NULL
  2. 示例：
  3. 1 |  INSERT into t_student VALUES (1004,null,"男",20);
  4. 提示报错：
  5. Column 'sname' cannot be null --提示名字的取值不能为null


默认值约束

默认值约束（DEFAULT）：
用来规定字段的默认值。如果某个被设置为DEFAULT约束的字段没插入具体值，那么该字段的值将会被默认值填充。

部分值插入必须要插入对应的列，否则运行下面语句不知道该插入那个字段里。

  1. 验证学生名字的取值不能为null。 --sname VARCHAR(5) not NULL
  2. 示例：
  3. 1 |  INSERT into t_student VALUES (1004,null,"男",20);
  4. 提示报错：
  5. Column 'sname' cannot be null --提示名字的取值不能为null

验证结果如下：
MySQL约束 - 图2

检查约束

检查约束（CHECK）:
用来限制某个字段的取值范围，可以定义为列级约束，也可以定义为表级约束。

  1. 限制取值范围：
  2. 1 |  CHECK(sex = '男'||sex = '女')
  3. 2 |  CHECK(age>=20 AND age<=50),
  5. 提示：Check constraint 't_student_chk_2' is violated.--年龄的值违反了取值范围
  6. 例：
  7. 1 |  INSERT into t_student VALUES (null,'haha4','和',20);
  8. 2 |  INSERT into t_student VALUES (null,'haha4','女',19);
  9. 2 |  INSERT into t_student VALUES (null,'haha4','女',60);

唯一约束📚

唯一约束(UNIQUE,缩写UK:
规定了一张表中指定的某个字段的值不能重复，即这一字段的每个值都是唯一的，如果想要某个字段的值不能重复，那么就可以为该字段添加为唯一约束。无论单个字段还是多个字段唯一约束的添加均可使用列级约束和表级约束。

  1. 提示：Duplicate entry '124@qq.com' for key 't_student.email'--邮箱的取值违反了唯一约束
  2. 示例：
  3. 1 |  iNSERT into t_student VALUES (default,"小明","女",21,"2020/2/3","测试一班","124@qq.com");
  4. 2 |  iNSERT into t_student VALUES (default,"小明","女",21,"2020/2/4","测试一班","124@qq.com");

约束从作用上可以分为两类：

约束从作用上可以分为两类：

（1）表级约束：
可以约束中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用‘,’分隔；必须指出要约束的列的名称；
（2）列级约束：
包含在列定义中，直接跟在该列的其它定义之后，用空格分隔；不必指定列名；

SQL展示：在创建数据库表时添加表级约束和列级约束

  1. 创建数据库表：
  2. 1 |  CREATE TABLE t_student (
  3.      sno int (5) auto_increment,
  4.      sname VARCHAR(5) not null,
  5.      sex CHAR(1) default "男",
  6.      age int(3) , 
  7.      enterdate date,
  8.      classname VARCHAR(10),
  9.      email VARCHAR(16),
  10.      constraint pk_stu primary key (sno),   --constraint pk_stu   pk_stu主键约束的名子
  11.      constraint ck_stu_sex check (sex = "男" || sex = "女"),
  12.      constraint ck_stu_age check (age >= 18 and age <= 58),
  13.      constraint uq_stu_email unique (email)
  14.      );

表级验证：

  1. 1 验证插入学生性别的值不是男女，SQL会不会报错。--constraint ck_stu_sex check (sex = "男" || sex = "女")
  2. 示例：
  3. 1 |  INSERT into t_student VALUES (1001,"夏利","有",27,"2020/2/8","测试一班","ial@qq.com");
  4. 提示：
  5.  Check constraint 'ck_stu_sex' is violated.--提示学生性别的取值范围错误
  8.  2 验证插入学生的年龄为59时，SQL会不会报错。--constraint ck_stu_age check (age >= 18 and age <= 58)
  9. 示例：
  10.  2 |  INSERT into t_student VALUES (10010,"夏","男",59,"2020/2/8","测试一班","ia76l@qq.com");
  11.  提示：
  12. Check constraint 'ck_stu_age' is violated.--提示学生的年龄取值范围错误
  15. 3 验证学生邮箱是不是唯一的。--constraint uq_stu_email unique (email)
  16. 示例：
  17. 3 |   INSERT into t_student VALUES (10010,"夏夏","男",19,"2020/2/8","测试一班","ia76l@qq.com")
  18. 3 |  INSERT into t_student VALUES (10011,"夏li","男",18,"2020/2/8","测试一班","ia76l@qq.com");
  19. 提示：
  20. Duplicate entry 'ia76l@qq.com' for key 't_student.uq_stu_email'--邮箱重复



在创建表以后添加约束

SQL展示：

  1. 创建数据库表：
  2. 1 | CREATE TABLE t_student (
  3.     sno int (5),
  4.     sname VARCHAR(5) not null,
  5.     sex CHAR(1) default "男",
  6.     age int(3) , 
  7.     enterdate date,
  8.     classname VARCHAR(10),
  9.     email VARCHAR(16)
  10.     );
  12. 在创建表以后添加约束
  13. 2 |  alter table t_student add constraint pk_stu primary key (sno); --主键约束 
  14. 2 |  alter table t_student modify sno int(6) auto_increment;   --修改自增条件
  15. 2 |  alter table t_student add constraint ck_stu_sex check (sex = "男" || sex = "女");
  16. 2 |  alter table t_student add constraint ck_stu_age check (age >= 18 and age <= 58);
  17. 2 |  alter table t_student add constraint uq_stu_email unique (email);

表后添加约束添加成功：查看表结构
MySQL约束 - 图3

非约束条件

约束条件用于对表中字段进行约束，一般写在某个字段最后，如果有多个约束条件，用空格分隔，创建新表时约束条件的写法如下：

  1. create table 表名称
  2. (
  3.     字段名称 数据类型[(长度) 约束条件1 约束条件2 ...],
  4.     ...
  5. );

约束

约束条件 约束描述
unsigned 无符号
zerofill 0填充
not null 非空
default 默认值
unique 唯一
auto_increment 自增
primary key 主键
foreign key 外键

unsigned - 无符号

  • 说明此字段为无符号整数类型

  • 语法

    1. create table 表名称
    2. (
    3.   字段名称 int unsigned,
    4.   ...
    5. );

    zerofill - 0填充

  • 定义了数据类型的长度，如果实际位数小于定义的长度，显示时会在左边用0填充

  • 语法
    1. create table 表名称
    2. (
    3.   字段名称 int zerofill,
    4.   ...
    5. );

not null - 非空

  • 在表中插入数据中这个字段不能为空

  • 语法

    1. create table 表名称
    2. (
    3.   字段名称 数据类型(长度) not null,
    4.   ...
    5. );

    default - 默认值

  • 在表中插入数据时，如果不对有默认值的字段赋值，该字段将使用默认值

  • 语法

    unique - 唯一

  • 在表中插入数据时，该字段的值是唯一的，不能与已有数据的该字段值重复

  • 语法

    1. create table 表名称
    2. (
    3.   字段名称 数据类型(长度) default 0,        # 默认值0
    4.   ...
    5. );

  • 联合唯一

    • 单个字段可以重复，但多个字段组合起来是唯一的
    • 语法
      1. create table 表名称
      2. (
      3. 字段名称1 数据类型[(长度) 约束条件],
      4. 字段名称2 数据类型[(长度) 约束条件],
      5. ...,
      6. unique(字段名称1,字段名称2)        # 字段名称1和字段名称2是联合唯一的
      7. );

auto_increment - 自增

  • 在表中插入数据时，如果不对该字段赋值，会自动在已有最大值的基础上+1

  • 语法

    1. create table 表名称
    2. (
    3.   字段名称 数据类型(长度) auto_increment,
    4.   ...
    5. );

    primary key - 主键

  • 主键一般与自增一起使用，也是约束唯一，但它还可以提高查询效率

  • 在外键中也经常绑定与主键，具体内容请移步外键内容

  • 语法

    1. create table 表名称
    2. (
    3.   字段名称 int primary key auto_increment,
    4.   ...
    5. )

    foreign key - 外键

  • 外键是表与表间关系的一种键