mysql 表的完整性约束

• 为防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束。
  MySQL中主要支持以下几种种完整性约束，如表所示。 其中Check约束是MySQL8中提供的支持。

约束条件	约束描述
PRIMARY KEY	主键约束，约束字段的值可唯一地标识对应的记录
NOT NULL	非空约束，约束字段的值不能为空
UNIQUE	唯一约束，约束字段的值是唯一的
CHECK	检查约束，限制某个字段的取值范围
AUTO_INCREMENT	自动增加约束，约束字段的值自动递增
FOREIGN KEY	外键约束，约束表与表之间的关系

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

表级约束

• 可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用','分隔；必须指出要约束的列的名称；

  CREATE TABLE t_student(
  	sno INT(6) auto_increment,
  	sname VARCHAR(5) NOT NULL,
  	sex CHAR(1) DEFAULT '男',
  	age INT(3) CHECK(age>=18 and age<=50),
  	enterdate DATETIME,
  	classname VARCHAR(10),
  	email VARCHAR(15),
  	CONSTRAINT pk_stu PRIMARY KEY(sno), -- pk_stu 主键约束的名字
  	CONSTRAINT ck_stu_sex CHECK(sex='男' || sex='女'),
  	CONSTRAINT ck_stu_age CHECK(age >= 18 || age <= 50),
  	CONSTRAINT uq_stu_email UNIQUE(email)
  );

列级约束

• 包含在列定义中，直接跟在该列的其它定义之后 ，用空格分隔；不必指定列名;

  CREATE TABLE t_student(
  	sno INT(6) PRIMARY KEY auto_increment,
  	sname VARCHAR(5) NOT NULL,
  	sex CHAR(1) DEFAULT '男' CHECK(sex='男' || sex='女'),
  	age INT(3) CHECK(age>=18 and age<=50),
  	enterdate DATETIME,
  	classname VARCHAR(10),
  	email VARCHAR(15) UNIQUE
  );

在创建表以后添加约束

ALTER TABLE t_student ADD CONSTRAINT pk_stu PRIMARY KEY(sno); -- 主键约束
ALTER TABLE t_student MODIFY sno INT(6) auto_increment; -- 修改自增条件
ALTER TABLE t_student ADD CONSTRAINT ck_stu_sex CHECK(sex='男' || sex='女');
ALTER TABLE t_student ADD CONSTRAINT ck_stu_age CHECK(age >= 18 and age <= 50);
ALTER TABLE t_student ADD CONSTRAINT ck_stu_email UNIQUE(email);

DESC t_student;

总结

主键约束

1.主键约束（PRIMARY KEY，缩写PK），是数据库中最重要的一种约束，其作用是约束表中的某个字段可以唯一标识一条记录。因此，使用主键约束可以快速查找表中的记录。就像人的身份证、学生的学号等等，设置为主键的字段取值不能重复（唯一），也不能为空（非空），否则无法唯一标识一条记录。 **主键 = 不能为空 + 唯一 **
2. 主键可以是单个字段，也可以是多个字段组合。
3. 对于单字段主键的添加可使用表级约束，也可以使用列级约束；而对于多字段主键的添加只能使用表级约束。

非空约束

1. 非空约束（NOT NULL，缩写NK）规定了一张表中指定的某个字段的值不能为空（NULL）。设置了非空约束的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。
2. 无论是单个字段还是多个字段非空约束的添加只能使用列级约束（非空约束无表级约束）
3. 为已存在表中的字段添加非空约束 ALTER TABLE t_student MODIFY sex VARCHAR(1) NOT NULL;
4. 使用ALTER TABLE语句删除非空约束

唯一约束

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

检查约束

1. 检查约束（CHECK）用来限制某个字段的取值范围，可以定义为列级约束，也可以定义为表级约束。MySQL8开始支持检查约束。

默认值约束

1. 认值约束（DEFAULT）值用来规定字段的默认。如果某个被设置为DEFAULT约束的字段没插入具体值，那么该字段的值将会被默认值填充。
2. 默认值约束的设置与非空约束一样，也只能使用列级约束。

字段值自动增加约束

1. 自增约束（AUTO_INCREMENT）可以使表中某个字段的值自动增加。一张表中只能有一个自增长字段，并且该字段必须定义了约束（该约束可以是主键约束、唯一约束以及外键约束），如果自增字段没有定义约束，数据库则会提示“Incorrect table definition; there can be only one auto column and it must be defined as a key”错误。
2. 由于自增约束会自动生成唯一的ID，所以自增约束通常会配合主键使用，并且只适用于整数类型。一般情况下，设置为自增约束字段的值会从1开始，每增加一条记录，该字段的值加1。

应用场景

1. 为已存在表中的字段添加自增约束

  ALTER TABLE 表名 MODIFY 字段 字段类型 auto_increment;
  ALTER TABLE t_student MODIFY sno int(4) auto_increment;

2. 为已存在表中的字段删除自增约束

  ALTER TABLE 表名 MODIFY 字段 字段类型;
  ALTER TABLE t_student MODIFY sno int(4);

外键约束

外键策略

总结

1. 主键 = 不能为空 + 唯一 ，主键的作用：可以通过主键查到唯一的一条记录
2. 主键可以是单个字段，也可以是多个字段组合
3. 如果主键没有设定值，或者用null.default都可以完成主键自增的效果。
4. 如果sql报错，可能主键就浪费了，后续插入的主键是不连号的。