14. DDL-约束的管理

        本小节主要讲述MySQL数据库的五种约束，包括每种约束的作用，如何创建和删除。

1. 约束的概念

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

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

2. 主键约束

2.1 主键约束作用

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

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

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

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

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

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 autr_increment
        在创建表指定某列为主键的同时，如添加autr_increment了关键字，就表示主键的值会自动增长，我们在插入新数据时，不用考虑主键的值了。
        例：创建学生表3，包含字段ID、学号、姓名，主键为ID，要求主键自增。

create table student3(
    id  int primary key auto_incremaent ,
    sno  int ,
    name varchar(10)
);

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

insert into student3(sno,name) values(20220101,'张三');
insert into student3(sno,name) values(20220102,'李四');

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

扩展：

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

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

3. 唯一约束

3.1 唯一约束作用

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

        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 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 创建非空约束

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

                cresate 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 外键约束作用

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

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

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

5.2 创建外键约束

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

                create table 表名(

                        列名1 数据类型 ,

                        列名2 数据类型 ,

                        … ,

                        foreing key (列名) references 引用列所在的表名(列名)

                        );

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

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

create table class(
    id int primary,
    name varchar(20) 
);

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

create table student7(
    id int primary key,
    sno int ,
    name varchar(10),
    classid int ,
    foreing key(classid) references class(id)
);

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

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

                alter table 表名 add constraint foreing 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,'张三');

8. 单元小结

        本单元重点讲述了5种约束：包括主键约束、唯一约束、非空约束、外键约束、检查性约束。重点需要掌握每种约束的作用、创建。遇到错误提示时，如何解决。