MySQL_08约束

2022-11-26 12:52:22

MySQL_08约束

1.什么是约束？

约束对应的英语单词：constraint

在创建表的时候，我们可以给表中的字段加一些约束，来保证这个表中数据的完整性、有效性。

约束的作用就是为了保证表中的数据有效。

2.常用的约束包括哪些？

约束名 关键字
非空约束 not null
唯一性约束 unique
主键约束 primary key(简称PK)
外键约束 foreign key(简称FK)
检查约束 check(MySQL不支持,Oracle支持)

3.非空约束：not null

3.1创建表，使用not null

非空约束not null约束的字段不能为null。

现创建一个t_vip表，其中有id和name两个字段，使用not null约束name字段，同时插入两条记录。

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) not null
);
insert into t_vip(id,name) values(1,'Zhangsan');
insert into t_vip(id,name) values(2,'Lisi');

3.2使用sql脚本文件批量执行sql语句

我们可以创建一个后缀为.sql的sql脚本文件，将以上命令语句一并粘贴进去。

MySQL_08约束

使用时，使用source命令导入该sql脚本文件，即可执行里面的所有命令。

输入source，然后直接将sql脚本文件拖进窗口，回车。

mysql> source D:\code\MySQL\MySQL_08约束\test01.sql

MySQL_08约束

这种方式适用于执行大量sql语句。

3.3测试

插入如下数据：

insert into t_vip(id) values(3);

报错：

mysql> insert into t_vip(id) values(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

可见，name的值已经不可为null。

mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

4.唯一性约束：unique

4.1创建表，使用unique

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) unique
);
insert into t_vip(id,name) values(1,'Zhangsan');
insert into t_vip(id,name) values(2,'Lisi');

4.2测试

查看表结构：

mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  | UNI | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

1）插入一条name重复的记录：

insert into t_vip(id,name) values(3,'Lisi');

插入失败：

mysql> insert into t_vip(id,name) values(3,'Lisi');
ERROR 1062 (23000): Duplicate entry 'Lisi' for key 'name'

2）插入两条只设定id的记录：

insert into t_vip(id) values(3);
insert into t_vip(id) values(4);

插入成功：

mysql> select * from t_vip;
+------+----------+
| id   | name     |
+------+----------+
|    1 | Zhangsan |
|    2 | Lisi     |
|    3 | NULL     |
|    4 | NULL     |
+------+----------+
4 rows in set (0.00 sec)

name字段虽然被unique约束了，但是可以都为null。

4.3两个字段联合唯一

现新建一个表t_vip，有id、name、email三个字段，要求name和email两个字段联合唯一。

也就是说可以插入的记录：

insert into t_vip(id,name,email) values(1,'Zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'Zhangsan','zhangsan@qq.com');

不可以插入的记录：

insert into t_vip(id,name,email) values(1,'Zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'Zhangsan','zhangsan@123.com');

创建表：

使用unique(字段名1,字段名2)来实现两个字段联合唯一。

约束没有添加在列的后面，这种约束被称为表级约束。

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255),
    email varchar(255),
    unique(name,email)
);
insert into t_vip(id,name,email) values(1,'Zhangsan','zhangsan@123.com');

测试：

1)插入与第一条记录name、email相同的记录。

报错

mysql> insert into t_vip(id,name,email) values(2,'Zhangsan','zhangsan@123.com');
ERROR 1062 (23000): Duplicate entry 'Zhangsan-zhangsan@123.com' for key 'name'

2)插入与第一条记录name相同，email不相同的记录。

插入成功

mysql> insert into t_vip(id,name,email) values(2,'Zhangsan','zhangsan@qq.com');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_vip;
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | Zhangsan | zhangsan@123.com |
|    2 | Zhangsan | zhangsan@qq.com  |
+------+----------+------------------+
2 rows in set (0.00 sec)

结论：当需要给多个字段联合起来添加某一个约束时，需要使用表级约束。

5.not null和unique联合

非空约束和唯一性约束可以联合起来使用。

新建表t_vip，使用not null 和unique联合约束name字段：

drop table if exists t_vip;
create table t_vip(
    id int, 
    name varchar(255) not null unique
);

查看表结构：

mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | NO   | PRI | NULL    |       |
+-------+--------------+------+-----+---------+-------+

在mysql中，如果一个字段同时被not null 和 unique联合约束，就自动变成了主键字段。(注意：oracle中不一样)

测试：

insert into t_vip(id,name) values(1,'ZhangSan');
insert into t_vip(id,name) values(2,'ZhangSan');//报错，name不可重复
insert into t_vip(id) values(2);//也报错，name不能为null

6.主键约束(primary key，简称PK)【重要】

6.1主键概述

主键约束的相关术语：

  • 主键字段：在一个字段上添加了主键约束，这样的字段就叫：主键字段。
  • 主键值：主键字段中每一个值都叫：主键值。

主键有什么用？

  • 主键值是每一行记录的唯一标识，相当于人的身份证号。

主键建议使用什么类型？

  • int
  • bigint
  • char
  • 等类型

注意：

  • 任何一张表都有主键，没有主键，表无效。

主键的特征：唯一且不为null(not null + unique)

如何给一张表添加主键约束？

drop table if exists t_vip;
create table t_vip(
    id int primary key,
    name varchar(255)
);

插入数据：

insert into t_vip(id,name) values(1,'ZhangSan');
insert into t_vip(id,name) values(2,'ZhangSan');
insert into t_vip(id,name) values(1,'Lisi');//报错，主键不可重复
insert into t_vip(name) values('Lisi');//报错，主键不可为null

查询表中数据：

mysql> select * from t_vip;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | ZhangSan |
+----+----------+
2 rows in set (0.00 sec)

此外，还可以通过表级约束来添加主键：

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255),
    primary key(id)
);

6.2复合主键

一个字段做主键叫单一主键，两个字段联合起来做主键叫复合主键。

通过表级约束来添加联合主键：

drop table if exists t_vip;
#id和name联合起来做主键
create table t_vip(
    id int,
    name varchar(255),
    primary key(id,name)
);

插入数据：

insert into t_vip(id,name) values(1,'ZhangSan');
insert into t_vip(id,name) values(1,'LiSi');//可以插入相同的id
insert into t_vip(id,name) values(1,'ZhangSan');//错误，不可插入相同的id和name

查询结果：

mysql> select * from t_vip;
+----+----------+
| id | name     |
+----+----------+
|  1 | LiSi     |
|  1 | ZhangSan |
+----+----------+
2 rows in set (0.00 sec)

在实际开发中，不建议使用复合主键。建议使用单一主键。

理由：

主键值存在的意义就是这行记录的身份证号，只要意义达到即可。单一主键就可以实现这一意义，复合主键比较复杂，不建议使用。

另外，在一张表中，只能有一个主键，不可重复定义。

drop table if exists t_vip;
mysql> create table t_vip(
    -> 	   id int primary key,
    ->     name varchar(255) primary key
    -> );
#报错，主键定义重复
ERROR 1068 (42000): Multiple primary key defined

6.3自然主键和业务主键

主键除了单一主键和复合主建外，还有如下分类：

  • 自然主键：主键值是一个自然数，和业务无关。
  • 业务主键：主键值和业务紧密关联，例如拿银行卡号来做主键值。

在实际开发中，使用自然主键较多一些，因为主键只需要做到不重复即可，不需要有意义。

业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动时，可能会影响到主键值，所以业务主键不建议使用。

6.4自动维护主键值机制

在mysql中，有一种机制，可以帮助我们自动维护主键值。

在主键后添加auto_increment：

drop table if exists t_vip;
create table t_vip(
    id int primary key auto_increment,
    name varchar(255)
);

插入记录，只设定name字段的值：

insert into t_vip(name) values('Zhangsan');
insert into t_vip(name) values('Zhangsan');
insert into t_vip(name) values('Zhangsan');
insert into t_vip(name) values('Zhangsan');
insert into t_vip(name) values('Zhangsan');
insert into t_vip(name) values('Zhangsan');

查看表中数据：

mysql> select * from t_vip;
+----+----------+
| id | name     |
+----+----------+
|  1 | Zhangsan |
|  2 | Zhangsan |
|  3 | Zhangsan |
|  4 | Zhangsan |
|  5 | Zhangsan |
|  6 | Zhangsan |
+----+----------+
6 rows in set (0.00 sec)

可见，如果未设定主键的值，主键会从1开始，以1递增地自动赋值。

7.外键约束(foreign key，简称FK)【重要】

外键约束涉及到的相关术语：

  • 外键约束：一种约束(foreign key)
  • 外键字段：添加了外键约束的字段
  • 外键值：外键字段中的每一个值

7.1设计数据库表描述“班级和学生”信息

业务背景:

请设计数据库表，来描述“班级和学生”的信息。

7.1.1第一种方案

班级和学生信息都存于一张表中

drop table if exists t_school;
create table t_school(
    no int primary key auto_increment,
    name varchar(255),
    classno int,
    classname varchar(255)
);
insert into t_school(no,name,classno,classname) values
(1,'Zhangsan',100,'高三1班'),
(2,'Lisi',100,'高三1班'),
(3,'WangWu',101,'高三2班'),
(4,'ZhaoLiu',101,'高三2班'),
(5,'Tom',100,'高三1班'),
(6,'Jerry',101,'高三2班'),
(7,'Lili',101,'高三2班'),
(8,'Lorry',100,'高三1班');

查看表：

+----+----------+---------+------------+
| no | name     | classno | classname  |
+----+----------+---------+------------+
|  1 | Zhangsan |     100 |  高三1班    |
|  2 | Lisi     |     100 |  高三1班    |
|  3 | WangWu   |     101 |  高三2班    |
|  4 | ZhaoLiu  |     101 |  高三2班    |
|  5 | Tom      |     100 |  高三1班    |
|  6 | Jerry    |     101 |  高三2班    |
|  7 | Lili     |     101 |  高三2班    |
|  8 | Lorry    |     100 |  高三1班    |
+----+----------+---------+------------+

分析以上方案的缺点：有多项数据重复，数据冗余，空间浪费，是比较失败的设计。

7.1.2第二种方案

班级一张表，学生一张表。

班级表：t_class

+---------+------------+
| classno | classname  |
+---------+------------+
|     100 |  高三1班    |
|     101 |  高三2班    |
+---------+------------+

学生表：t_student

+----+----------+------+
| no | name     |  cno |#(cno班级编号)
+----+----------+------+
|  1 | Zhangsan |  100 |
|  2 | Lisi     |  100 |
|  3 | WangWu   |  101 |
|  4 | ZhaoLiu  |  101 |
|  5 | Tom      |  100 |
|  6 | Jerry    |  101 |
|  7 | Lili     |  101 |
|  8 | Lorry    |  100 |
+----+----------+------+

当cno字段没有任何约束时，可能会导致数据无效。比如可能出现一个102，但是102班级不存在。

所以为了保证cno字段中的值都是100和101，需要给cno字段添加一个外键约束。

即使用FK引用t_class这张表的classno。

添加后，cno字段就是外键字段，cno字段中的每一个值都是外键值。

注意：

  • 被引用的t_class是父表，t_student是字表。
  • 创建表的顺序是：
    • 先创建父表，再创建子表。
  • 删除表的顺序是：
    • 先删子表，再删父表。
  • 插入数据的顺序是：
    • 先插入父，再插入子。
  • 删除数据的顺序是：
    • 先删除子，再删除父。

开始创建表：

#先删子，再删父
drop table if exists t_student;
drop table if exists t_class;
#先创父，再创子
create table t_class(
    classno int primary key,
    classname varchar(255)
);
create table t_student(
    no int primary key auto_increment,#自增
    name varchar(255),
    cno int,
    foreign key(cno) references t_class(classno)#添加外键约束，引用t_class表中的classno数据
);

#先插父，再插子
insert into t_class(classno,classname) values(100,'高三1班');
insert into t_class(classno,classname) values(101,'高三2班');

insert into t_student(no,name,cno) values
(1,'Zhangsan',100),
(2,'Lisi',100),
(3,'WangWu',101),
(4,'ZhaoLiu',101),
(5,'Tom',100),
(6,'Jerry',101),
(7,'Lili',101),
(8,'Lorry',100);

思考：子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？

所以被引用的这个字段不一定是主键。但被引用的这个字段必须是唯一的，因为如果t_class表中的classno字段可以重复，那么在t_student表中，cno所引用的classno字段也就不确定了。

那么外键值可以为null吗？

插入一条cno为null的记录：

insert into t_student(name) values('Alice');

查看表：

mysql> select * from t_student;
+----+----------+------+
| no | name     | cno  |
+----+----------+------+
|  1 | Zhangsan |  100 |
|  2 | Lisi     |  100 |
|  3 | WangWu   |  101 |
|  4 | ZhaoLiu  |  101 |
|  5 | Tom      |  100 |
|  6 | Jerry    |  101 |
|  7 | Lili     |  101 |
|  8 | Lorry    |  100 |
|  9 | Alice    | NULL |
+----+----------+------+
9 rows in set (0.00 sec)

可见，外键值可以为null。

7.2总结

  1. 在设计表时，要考虑空间问题
  2. 实现父表和子表：
    • 创建表：先创父，再创子
    • 删除表：先删子，再删父
    • 插入数据：先插父，再插子
    • 删除数据：先删子，再删父
  3. 添加外键约束：foreign key(字段名) references 被引用表(被引用字段)。
  4. 外键值可以为null。
  5. 父表中被引用的字段不一定是主键，但必须唯一。(用unique字段约束)
上一篇：爬虫实现qq音乐歌单无vip批量下载


下一篇：Java总结篇系列：Java多线程（四）