4. MySQL 约束

• 在 MySQL 中， 约束 是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。

例如，在数据表中存放年龄的值时，如果存入 200、300 这些无效的值就毫无意义了。因此，使用约束来限定表中的数据范围是很有必要的。

【 1. 主键约束 PRIMARY KEY 】

• 主键（PRIMARY KEY） 的完整称呼是 主键约束，是 MySQL 中使用最为频繁的约束。一般情况下，为了便于 DBMS 更快的查找到表中的记录，都会在表中设置一个主键。
• 每个表只能定义一个主键 ，其它约束可以有多个。
• 唯一性原则 ：主键字段的值在表中必须是唯一的，不存在重复的情况，且不能为 NULL。即 设置成主键约束的字段的值中不可能存在有相同主键值的数据，且不能为NULL ；否则，无法设置主键约束。
• 主键分为单字段主键和多字段联合主键。在创建数据表时设置主键约束，既可以为表中的一个字段设置 单字段主键 ，也可以为表中多个字段设置 联合主键 。

1.1 在创建表时设置主键约束

设置单字段主键

• 在 CREATE TABLE 语句中，通过 在字段后面加 PRIMARY KEY 关键字来指定主键 。基本语法：

<字段名> <数据类型> PRIMARY KEY [默认值]

• 实例
  在 test_db 数据库中创建 tb_emp3 数据表，其主键为 id
  CREATE TABLE tb_emp3
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
  
• 也可以在定义完所有字段之后指定主键，语法格式如下：

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

• 实例
  在数据库中创建 tb_emp4 数据表，其主键为 id。
  CREATE TABLE tb_emp4
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id)
);
  

在创建表时设置联合主键

• 联合主键 就是这个主键是由一张表中多个字段组成的约束，是指使用 表中的两个或更多字段的组合来共同作为主键，以此来唯一标识表中的记录 。当单个字段无法唯一标识表中的每一行数据，或者业务逻辑需要多个字段的组合来定义唯一性时，就会使用联合主键。
• 最小化原则 ：联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。

• 比如，设置学生选课数据表时，使用学生编号做主键还是用课程编号做主键呢？如果用学生编号做主键，那么一个学生就只能选择一门课程。如果用课程编号做主键，那么一门课程只能有一个学生来选。显然，这两种情况都是不符合实际情况的。
• 实际上设计学生选课表，要限定的是一个学生只能选择同一课程一次。因此，学生编号和课程编号可以放在一起共同作为主键，这也就是联合主键了。

• 基本语法
  • 一个字段名只能在联合主键字段表中出现一次 。
  • 当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。

PRIMARY KEY [字段1，字段2，…,字段n]

• 实例
  创建数据表 tb_emp5，假设表中没有主键 id，为了唯一确定一个员工，可以把 name、deptId 联合起来作为主键。
  CREATE TABLE tb_emp5
(
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(name,deptId)
);
  

1.2 在修改表时添加主键约束

• 基本语法

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

• 实例
  修改数据表 student，将字段 id 设置为主键。
  ALTER TABLE student ADD PRIMARY KEY(id);
  

1.3 删除主键约束

• 由于主键约束在一个表中只能有一个，因此不需要指定主键名就可以删除一个表中的主键约束。
  基本语法：

ALTER TABLE <数据表名> DROP PRIMARY KEY;

• 实例
  删除 student 表中的主键约束。
  ALTER TABLE student DROP PRIMARY KEY;
  

1.4 主键自增长 AUTO_INCREMENT

• 在 MySQL 中，当主键定义为 自增长 后，这个主键的值就不再需要用户输入数据了，而是由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。
  通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长，基本语法
  • 默认情况下，AUTO_INCREMENT 的 初始值是 1，每 新增一条记录，字段值自动加 1。因此，这个字段不可能出现相同的值。
  • 一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
  • AUTO_INCREMENT 约束的字段必须具备 非空 NOT NULL 属性 。
  • AUTO_INCREMENT 约束的字段 只能是整数类型 （TINYINT、SMALLINT、INT、BIGINT 等）。
  • AUTO_INCREMENT 约束字段的 最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

字段名 数据类型 AUTO_INCREMENT

• 实例
  1. 创建名为 tb_student 的数据表。其中，id 为主键，每插入一条新记录，id 的值就会在前一条记录的基础上自动加 1。name 为非空字段，该字段的值不能为空值（NULL）。
     CREATE TABLE tb_student(
id INT(4) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL
);
  2. 向 tb_student 表中插入数据。
     INSERT INTO tb_student(name,id) VALUES('Java',NULL);
INSERT INTO tb_student(name,id) VALUES('Python',NULL);
INSERT INTO tb_student(name,id) VALUES('SQL',NULL);
  3. 查看表。
     SELECT * FROM tb_student;
     

指定自增字段初始值

• 如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。
  例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加。
• 实例
  1. 创建表 tb_student2，指定主键从 100 开始自增长。
     CREATE TABLE tb_student2 (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
PRIMARY KEY(ID)
)AUTO_INCREMENT=100;
  2. 向 tb_student2 表中插入数据。
     INSERT INTO tb_student2 (name)VALUES('Java');
INSERT INTO tb_student2 (name)VALUES('Python');
  3. 使用 SELECT 命令查询表中记录
     SELECT * FROM tb_student2;
     

自增字段不连续

• 当唯一键冲突是，再插入新的数据时自增字段会不连续。
• 实例
  1. 创建表 tb_student3，其中 id 是自增主键字段，name 是唯一索引。
     CREATE TABLE tb_student3(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) UNIQUE KEY,
age INT DEFAULT NULL
);
  2. 向 tb_student3 表中插入数据。
     INSERT INTO tb_student3 VALUES(1,1,1);
  3. 此时，表 tb_student3 中已经有了（1,1,1）这条记录，这时再执行一条插入数据命令会报错。
     这是由于表中已经存在 name=1 的记录，所以报 Duplicate key error（唯一键冲突）。
     INSERT INTO tb_student3 VALUES(null,1,1);
  4. 在这之后，再插入新的数据时，自增 id 就是 3，这样就出现了自增字段值不连续的情况。
     INSERT INTO tb_student3 VALUES(null,2,66);
  5. 查看结果
     SELECT * FROM tb_student3;
     

【 2. 外键约束 FOREIGN KEY 】

• MySQL 外键约束（FOREIGN KEY） 是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是 主表（父表） ，外键所在的表就是 从表（子表） 。外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性 。
  比如，一个水果摊，只有苹果、桃子、李子、西瓜等 4 种水果，那么，你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜，其它的水果都是不能购买的。
• 定义外键时，需要遵守下列规则：
  • 主表必须已经存在于数据库中，或者是当前正在创建的表。如果主表当前正在创建，则主表与从表是同一个表，这样的表称为 自参照表 ，这种结构称为 自参照完整性 。
  • 必须为主表定义主键 。
  • 一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值 。
  • 在主表的表名后面指定列名或列名的组合，这个列或列的组合必须是主表的主键或候选键。
  • 从表的外键关联的必须是主表的主键，外键中列的数目、列的数据类型必须和主表主键相同 。
    例如，两者都是 INT 类型，或者都是 CHAR 类型。如果不满足这样的要求，在创建从表时，就会出现“ERROR 1005(HY000): Can’t create table”错误。
• 主表删除某条记录时，从表中与之对应的记录也必须有相应的改变 。

2.1 在创建表时设置外键约束

• 基本语法：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

• 实例
  1. 创建1个数据表 tb_dept1，并在 tb_dept1 的 id 字段上创建主键约束。
     CREATE TABLE tb_dept1
(
id INT(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);
  2. 创建数据表 tb_emp6，并在表 tb_emp6 上创建外键约束，让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id。
     CREATE TABLE tb_emp6
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept1
FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);
     

2.2 在修改表时设置外键约束

• 外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须全部来源于主键列或者是为空没有数据，不过为空的数据跟与之相关联的表就没有关系了，一般不采用 。
  基本语法：

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

• 实例
  修改数据表 tb_emp3，将字段 deptId 设置为外键，与数据表 tb_dept1 的主键 id 进行关联。
  ALTER TABLE tb_emp3
ADD CONSTRAINT fk_tb_dept1_2
FOREIGN KEY(deptId)
REFERENCES tb_dept1(id);
  

2.3 删除外键约束

• 基本语法

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

• 实例
  删除数据表 tb_emp3 中的外键约束 fk_tb_dept1_2。
  ALTER TABLE tb_emp3 DROP FOREIGN KEY fk_tb_dept1_2;
  

【 3. 唯一约束 UNIQUE KEY 】

• MySQL 唯一约束（Unique Key） 是指 所有记录中字段的值不能重复出现 。
  例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。如果其中一条记录的 id 值为‘0001’，那么该表中另一条记录的 id 值就不能为‘0001’。
• 唯一约束与主键约束相似的是它们都可以 确保列的唯一性 。不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许只能有一个空值。而主键约束在一个表中只能有一个，且不允许有空值 。
  比如，在用户信息表中，为了避免表中用户名重名，可以把用户名设置为唯一约束。

3.1 在创建表时设置唯一约束

• 唯一约束可以在创建表时直接设置，通常设置在除了主键以外的其它列上。基本语法：
  • 在定义完列之后直接使用 UNIQUE 关键字指定唯一约束。

<字段名> <数据类型> UNIQUE

• 实例
  创建数据表 tb_dept2，指定部门的名称唯一。
  CREATE TABLE tb_dept2
(
id INT(11) PRIMARY KEY,
name VARCHAR(22) UNIQUE,
location VARCHAR(50)
);
  

3.2 在修改表时添加唯一约束

• 基本语法

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

• 实例
  ALTER TABLE tb_dept1 ADD CONSTRAINT unique_name UNIQUE(name);
  

3.3 删除唯一约束

• 基本语法

ALTER TABLE <表名> DROP INDEX <唯一约束名>;

• 实例
  ALTER TABLE tb_dept1 DROP INDEX unique_name;
  

【 4. 检查约束 CHECK 】

• MySQL 检查约束（CHECK） 是用来 检查数据表中字段值有效性 的一种手段，设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。

4.1 选取设置检查约束的字段

• 在更新表数据的时候，系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件。MySQL 可以使用简单的表达式来实现 CHECK 约束，也允许使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。
• 基本语法
  • “表达式”指的就是 SQL 表达式，用于指定需要检查的限定条件。

CHECK <表达式>

• 若将 CHECK 约束子句置于表中某个列的定义之后，则这种约束也称为 基于列的 CHECK 约束 。
• 若将 CHECK 约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为 基于表的 CHECK 约束 。该约束可以同时对表中多个列设置限定条件。

4.2 在创建表时设置检查约束

• 在创建表时就可以为字段设置检查约束。
  基本语法：

CHECK(<检查约束>)

• 实例
  创建 tb_emp7 数据表，要求 salary 字段值大于 0 且小于 10000。
  CREATE TABLE tb_emp7
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CHECK(salary>0 AND salary<100),
FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);
  

4.3 在修改表时添加检查约束

• 如果一个表创建完成，可以通过修改表的方式为表添加检查约束。
  基本语法：

ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

• 实例
  修改 tb_emp7 数据表，要求 id 字段值大于 0。
  ALTER TABLE tb_emp7
ADD CONSTRAINT check_id
CHECK(id>0);
  

4.4 删除检查约束

• 基本语法

ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;

• 实例
  删除 tb_emp7 表中的 check_id 检查约束。
  ALTER TABLE tb_emp7
DROP CONSTRAINT check_id;
  

【 5. 默认值约束 Default 】

• 默认值（Default）的完整称呼是 默认值约束(Default Constraint) ，用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，系统就会自动为这个字段插入默认值。

5.1 在创建表时设置默认值约束

• 基本语法
  • 其中，“默认值” 为该字段设置的默认值，如果是字符类型的，要用单引号括起来。
  • 在创建表时为列添加默认值，可以一次为多个列添加默认值，需要注意不同列的数据类型。

<字段名> <数据类型> DEFAULT <默认值>;

• 实例
  创建数据表 tb_dept3，指定部门位置默认为 Beijing，新插入的记录如果没有指定部门位置，则默认都为 Beijing。
  CREATE TABLE tb_dept3
(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
location VARCHAR(50) DEFAULT 'Beijing'
);
  

5.2 在修改表时添加默认值约束

• 基本语法

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

• 实例
  修改数据表 tb_dept3，将部门位置的默认值修改为 Shanghai。
  ALTER TABLE tb_dept3
CHANGE COLUMN location
location VARCHAR(50) DEFAULT 'Shanghai';
  

5.3 删除默认值约束

• 当一个表中的列不需要设置默认值时，就需要从表中将其删除。
  基本语法：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

• 实例
  修改数据表 tb_dept3，将部门位置的默认值约束删除。
  ALTER TABLE tb_dept3
CHANGE COLUMN location
location VARCHAR(50) DEFAULT NULL;
  

【 6. 非空约束 NOT NULL 】

• MySQL 非空约束（NOT NULL） 指 字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。在表中某个列的定义后加上关键字 NOT NULL 作为限定词，来约束该列的取值不能为空。

比如，在用户信息表中，如果不添加用户名，那么这条用户信息就是无效的，这时就可以为用户名字段设置非空约束。

6.1 在创建表时设置非空约束

• 基本语法

<字段名> <数据类型> NOT NULL;

• 实例
  创建数据表 tb_dept4，指定部门名称不能为空。
  CREATE TABLE tb_dept4
(
id INT(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);
  

6.2 在修改表时添加非空约束

• 如果在创建表时忘记了为字段设置非空约束，也可以通过修改表进行非空约束的添加。
  基本语法：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NOT NULL;

• 实例
  修改数据表 tb_dept4，指定部门位置不能为空。
  ALTER TABLE tb_dept4
CHANGE COLUMN location
location VARCHAR(50) NOT NULL;
  

6.3 删除非空约束

• 基本语法

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;

• 实例
  修改数据表 tb_dept4，将部门位置的非空约束删除。
  ALTER TABLE tb_dept4
CHANGE COLUMN location
location VARCHAR(50) NULL;
  

【 7. 查看表中的约束 】

• 基本语法

SHOW CREATE TABLE <数据表名>;

• 实例
  1. 创建数据表 tb_emp8 并指定 id 为主键约束，name 为唯一约束，deptId 为非空约束和外键约束。
     CREATE TABLE tb_emp8
(
id INT(11) PRIMARY KEY,
name VARCHAR(22) UNIQUE,
deptId INT(11) NOT NULL,
salary FLOAT DEFAULT 0,
CHECK(salary>0),
FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);
  2. 查看表中的约束SHOW CREATE TABLE tb_emp8 \G