14.DDL约束的管理

1. 约束的概念

约束，英文单词为Constraint，是定义在列上的一种强制规则。用来保证数据库中数据的正确性、有效性和完整性。数据库通常都有如下五种约束。

约束名称	中文名
PRIMARY KEY	主键约束
UNIQUE	唯一约束
NOT NULL	非空约束
FOREIGN KEY	外键约束
CHECK	检查性约束 (MySQL8.0.16新增约束)

2. 主键约束

2.1 主键约束作用

主键(PRIMARY KEY)：用来唯一标识表中的一条记录。

如下是学生表中的两条学生记录。姓名、姓名、年龄都相同，如果没有主键，就无法区分哪条记录到底对应哪个学生。

NAME	GENDER	AGE
张三	男	19
张三	男	19

如果要解决这个问题，可以在上面的学生表中添加一个主键，学号列，每个学生都赋予不同的学号，这样就可以区分姓名相同的两个学生了，如下：

SNO	NAME	GENDER	AGE
1	张三	男	19
2	张三	男	19

通常，在设计表结构时，主键应遵守如下规则：

1、每个表都要有主键，且一个表最多只能有一个主键。

2、主键所定义的列，不允许插入NULL值。

3、主键是给开发人员使用的，不是给最终客户使用的。因此，通常不用编号、姓名这种对最终用户有意义且可见的列作为主键，而是单独给表设计一个无需显示，也没有实际意义的列作为主键。

如上面的学生表，还可以进一步优化，添加一个ID列作为主键，如下：

ID	SNO	NAME	GENDER	AGE
1	1	张三	男	19
2	2	张三	男	19

这样调整之后，ID作为主键，是给开发人员使用的，对最终用户没有意义，不用在页面显示，因此最终用户无法修改。而学号列，对最终用户是有实际意义的，用户一旦输入错误，应该允许用户修改。

2.2 创建主键约束

可以在创建表的同时，设置某列为主键，语法如下：

CREATE TABLE 表名(

列名1 数据类型 PRIMARY KEY ,

列名2 数据类型 ,

…

);

也可以在创建表之后，后给表追加主键，语法如下：

ALTER TABLE 表名 ADD PRIMARY KEY (column)； --理解就可以

例：创建学生表1，包含字段ID、学号、姓名，主键为ID，要求在创建表的同时就创建主键。

CREATE TABLE student1(
    id  int PRIMARY KEY ,
    sno  int ,
    name varchar(10)
);

例：创建学生表2，包含字段ID、学号、姓名，主键为ID，要求在创建表之后追加主键。

CREATE TABLE student2(
    id  int  ,
    sno  int ,
    name varchar(10)
);
--追加主键
ALTER TABLE student2 ADD PRIMARY KEY (id);

再次使用Desc命令查看表结构，发现ID也已经设置为主键。

2.3 验证主键作用

向学生表1中插入如下数据，观察主键作用：

INSERT INTO student1(id,sno,name) VALUES(1,20220101,'张三');
INSERT INTO student1(id,sno,name) VALUES(1,20220102,'李四');

上述第一条语句执行成功，第二条语句返回'Duplicate entry 1 for key student1.PRIMARY'的错误提示，含义是主键重复。这就是主键的作用，用来限制重复数据。

再执行下属语句，

INSERT INTO student1(id,sno,name) VALUES(NULL,20220103,'王五');

同样给出错误提示，表示主键列ID不能为空。

2.4 删除主键约束

删除主键语法如下：

ALTER TABLE 表名 DROP PRIMARY KEY; --理解就可以

2.5 主键自增策略

在每次插入新记录时，如果我们自己添加主键的值，很有可能重复。因此，MySQL提供了主键自增策略，用来解决该问题。用法如下：

列名 数据类型 PRIMARY KEY AUTO_INCREMENT

在创建表指定某列为主键的同时，如添加AUTO_INCREMENT了关键字，就表示主键的值会自动增长，我们在插入新数据时，不用考虑主键的值了。

例：创建学生表3，包含字段ID、学号、姓名，主键为ID，要求主键自增。

CREATE TABLE student3(
    id  int PRIMARY KEY AUTO_INCREMENT  ,
    sno  int ,
    name varchar(10)
);

向表中插入两条数据进行验证：

INSERT INTO student3(sno,name) VALUES(20220101,'张三');
INSERT INTO student3(sno,name) VALUES(20220102,'李四');

通过上述案例可以看出，虽然插入的两条语句，没有指定ID列的值，但因为ID有AUTO_INCREMENT，所以ID列每次都是自动增1的。

扩展：

1、默认情况下AUTO_INCREMENT 的开始值是1，如果希望修改开始值不为1，可以使用如下SQL：

ALTER TABLE 表名 AUTO_INCREMENT = 开始值;
2、主键所定义列的数据类型必须是整型，AUTO_INCREMENT才有效。

3. 唯一约束

3.1 唯一约束作用

唯一约束(UNIQUE KEY)，也是用来唯一标识表中的一条记录的，在这张表中这个列的值不能重复。和主键约束有两点不同：

1、一个表可以有多个唯一约束，但主键约束最多只能有一个。

2、主键约束所定义的列，不允许有NULL值，而唯一约束所定义的列，允许有NULL值。

员工的电子邮箱或身份证号通常都是唯一的，可以定义为唯一约束。

3.2 创建唯一约束

创建唯一约束也有两种方式，可以在创建表的同时，指定唯一约束，语法如下：

CREATE TABLE 表名(

列名1 数据类型 UNIQUE,

列名2 数据类型 ,

…

);

也可以在创建表之后，后追加唯一约束，语法如下：

ALTER TABLE 表名 ADD UNIQUE(列名); --理解即可

例：创建学生表4，包含字段ID、学号、姓名，身份证号，要求身份证号唯一。

CREATE TABLE student4(
    id  int PRIMARY KEY ,
    sno  int ,
    name varchar(10),
    idCard char(18) UNIQUE
);

例：创建学生表5，包含字段ID、学号、姓名，身份证号，要求身份证号唯一，要求在创建表的之后追加唯一约束。

CREATE TABLE student5(
    id  int PRIMARY KEY ,
    sno  int ,
    name varchar(10),
    idCard char(18) 
);

ALTER TABLE student5 ADD UNIQUE(idCard);

再次使用Desc命令查看表结构，发现idCard也已经设置为唯一约束。

3.3 验证唯一约束作用

向学生表5中插入如下数据，观察唯一约束作用：

INSERT INTO student5(id,sno,name,idcard) VALUES(1,20220101,'张三','210102199901012345');
INSERT INTO student5(id,sno,name,idcard) VALUES(2,20220102,'李四','210102199901012345');

上述第一条语句执行成功，第二条语句返回'Duplicate entry '210102199901012345' for key student5.idCard'的错误提示，含义是违反唯一约束。

3.4 删除唯一约束

ALTER TABLE 表名 DROP INDEX 列名; ----理解即可

这种情况默认该列为此约束的名字，并且一定是在自己没有给其定名字的情况下才能用 。

SHOW INDEX IN 表名; 显示当前表中索引--理解即可

4. 非空约束

4.1 非空约束作用

非空约束(NOT NULL)，用来限定某列的所有行数据不能为NULL值。

4.2 创建非空约束

可以在创建表的同时，设置某列为为非空，语法如下：

CREATE TABLE 表名(

列名1 数据类型 ,

列名2 数据类型 NOT NULL,

…

);

也可以在创建表之后，修改某列为非空/空，语法如下：

ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL/NULL； ----理解即可

例：创建学生表6，包含字段ID、学号、姓名，要求ID为主键，姓名为非空。

CREATE TABLE student6(
    id  int PRIMARY KEY ,
    sno  int ,
    name varchar(10) NOT NULL
);

例：修改学生表6，学号为非空。

ALTER TABLE student6 MODIFY sno int NOT NULL;

4.3 验证非空约束作用

向学生表6中插入如下数据，观察非空约束作用：

INSERT INTO student6(id,sno,name) VALUES(1,20220101,'张三');
INSERT INTO student6(id,sno,name) VALUES(2,20220102,NULL);

上述第一条语句执行成功，第二条语句返回'Column name cannot be null'的错误提示，含义是姓名列不允许为空。

4.4 删除非空约束

ALTER TABLE 表名 MODIFY 列名 数据类型 NULL； ----理解即可

5. 外键约束

5.1 外键约束作用

外键约束，也叫FOREIGN KEY，是定义在列及引用列上建立的一种强制依赖关系。外键约束涉及到两个列，一个是外键所定义的列，一个是引用列。

外键约束的作用：限制外键所定义的列的值必须来源于引用列的值。

如：部门表中的部门编号列包含以下几种值 {10、20、30、40}，如果想让员工表中的部门编号列的取值只能在这个范围内，就可以通过在员工表的部门编号字段建立外键约束来达到目的。

5.2 创建外键约束

可以在创建表的同时，设置外键约束，语法如下：

CREATE TABLE 表名(

列名1 数据类型 ,

列名2 数据类型 ,

… ,

FOREIGN KEY(列名) REFERENCES 引用列所在的表名(列名)

);

注意：外键约束需要写在所有列的最后。

例：创建班级表，包括班级编号(主键)、班级名称列。

CREATE TABLE class(
    id  int  PRIMARY KEY,
    name varchar(20) 
);

例：创建学生表7，包含字段ID(主键)、学号、姓名、所属班级编号，班级编号为外键，引用班级表的班级编号列。

CREATE TABLE student7(
    id  int PRIMARY KEY ,
    sno  int ,
    name varchar(10),
    classid int ,
    FOREIGN KEY(classid)  REFERENCES class(id)
);

出现上述MUL标识，表示外键约束添加成功。

也可以在创建表之后追加外键约束，语法如下：

ALTER TABLE 表名 ADD CONSTRAINT FOREIGN KEY(列名) REFERENCES 引用表(引用列); ---理解即可

注：外键约束的引用列，必须有主键约束或者唯一约束

5.3 验证外键约束作用

向学生表7中插入如下数据，观察外键作用：

INSERT INTO student7(id,sno,name,classid) VALUES(10,20220101,'张三',10);

上述语句执行失败，出现错误提示，违反了外键约束。因为外键所约束的列classid，引用了class表的id字段，而class表中的id字段，没有10号班级，所以出现错误。

解决办法：向class表中插入id为10的班级信息或者设置student7表中的classid为空

5.4 删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 约束名 ; ----理解即可

注意：每一个约束都有一个名字，此处的约束名是创建约束时，我们指定的，如果没有指定，系统会默认生成一个。可以通过如下命令，来查看每个表中的约束名，显示结果中 key_name 字段为约束名。

SELECT * FROM information_schema.TABLE_CONSTRAINTS;

找到对应的constraint_name，进行删除--理解即可

6. 检查性约束

6.1 检查性约束作用

检查性约束（MySQL8.0.16新增约束）是确保某个列的所有行数据都必须满足的一个条件表达式。如性别列只能为男或女，年龄列只能在1岁到150岁之间，就可以通过检查性约束来完成。

6.2 创建检查性约束

检查性约束的创建语法如下：

CREATE TABLE 表名(

列名1 数据类型 CHECK (条件表达式),

列名2 数据类型 ,

…

);

例：创建学生表8，包含字段ID(主键)、学号、姓名，性别，年龄，要求性别列上有检查性约束，只能为男或女，年龄上有检查性约束，只能在15到30之间。

CREATE TABLE student8(
    id  int PRIMARY KEY ,
    sno  int ,
    name varchar(10),
    gender char(2) CHECK (gender in ('男','女')),
    age int CHECK (age >= 15 AND age <=30)
);

6.3 验证检查性约束作用

向学生表8中插入如下数据，观察检查性约束作用：

INSERT INTO student8(id,sno,name,gender,age) VALUES(10,20220101,'张三','F',20);

上述语句执行失败，出现错误提示，违反检查性约束。因为gender列有检查性约束，只能输入男或女。下列语句执行成功。

INSERT INTO student8(id,sno,name,gender,age) VALUES(10,20220101,'张三','男',20);

6.4 删除检查性约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名; --理解即可

也可以通过SHOW CREATE TABLE 表名，查看某个表的约束名。

7. 默认值

默认值也可以成为叫默认值约束，作用是给某个字段/某列指定默认值，一旦我们设置了默认值约束之后,在插入数据时,如果没有显式赋值,则会赋默认值 。

语法如下：

CREATE TABLE 表名(

列名1 数据类型 DEFAULT 默认值,

列名2 数据类型 ,

…

);

例：创建学生表9，包含字段ID(主键)、学号、姓名，主键为ID，所在城市，默认值为沈阳。

CREATE TABLE student9(
    id  int  PRIMARY KEY,
    sno  int ,
    name varchar(10),
    city varchar(20) DEFAULT '沈阳'
);

向学生表9中插入如下数据，观察默认值作用：

INSERT INTO student9(id,sno,name) VALUES(10,20220101,'张三');