MySQL_08约束

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多线程(四)