【MySQL】第七弹---深入理解数据库表约束：自增长、唯一键、外键及综合案例解析

✨个人主页：熬夜学编程的小林

💗系列专栏：【C语言详解】【数据结构详解】【C++详解】【Linux系统编程】【MySQL】

1 表的约束

1.1 自增长

1.2 唯一键

1.3 外键

1.4 综合案例

1 表的约束

1.1 自增长

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。
自增长的特点:

任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
自增长字段必须是整数
一张表最多只能有一个自增长

案例：

创建表

# 自增的字段需要有索引，类型为整数，且只能有一个
mysql> create table t1 (id int primary key auto_increment,name varchar(10) not null );
Query OK, 0 rows affected (0.02 sec)mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

插入数据

mysql> insert into t1 (name) values('张三'); # 只插入name，id默认从1开始
Query OK, 1 row affected (0.01 sec)mysql> select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)mysql> insert into t1 values(100,'张三'); # id插入指定的数，则插入指定的数
Query OK, 1 row affected (0.00 sec)mysql> select * from t1;
+-----+--------+
| id  | name   |
+-----+--------+
|   1 | 张三   |
| 100 | 张三   |
+-----+--------+
2 rows in set (0.00 sec)mysql> insert into t1 (name) values('李四'); # 再插入数值，不指定id，默认+1
Query OK, 1 row affected (0.00 sec)mysql> select * from t1;
+-----+--------+
| id  | name   |
+-----+--------+
|   1 | 张三   |
| 100 | 张三   |
| 101 | 李四   |
+-----+--------+
3 rows in set (0.00 sec)

mysql怎么知道我们插入前的数值是多少呢？

mysql> show create table t1 \G;
*************************** 1. row ***************************Table: t1
Create Table: CREATE TABLE `t1` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8 # AUTO_INCREMENT表示下次插入值的默认值
1 row in set (0.00 sec)

在插入后获取上次插入的 AUTO_INCREMENT 的值（批量插入获取的是第一个值）

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              101 |
+------------------+
1 row in set (0.00 sec)

索引：

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

1.2 唯一键

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

关于唯一键和主键的区别：我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。乍一听好像没啥区别，我们举一个例子

假设一个场景(当然，具体可能并不是这样，仅仅为了帮助大家理解)
比如在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一个是员工工号，我们可以选择身份号码作为主键。
而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。
具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯一键。
一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整。

案例：

创建表

# 给id 设置唯一键，也可以只写unique
mysql> create table t2(id char(10) unique key comment '学号，不能重复',-> name varchar(10) comment '姓名');
Query OK, 0 rows affected (0.02 sec)mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | char(10)    | YES  | UNI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

插入数据

mysql> insert into t2 values(1,'张三');
Query OK, 1 row affected (0.01 sec)mysql> insert into t2 values(1,'李四'); # 插入id = 1的数据，因为唯一键的约束不能插入重复的数据
ERROR 1062 (23000): Duplicate entry '1' for key 'id'mysql> insert into t2 values(2,'李四');
Query OK, 1 row affected (0.00 sec)mysql> select * from t2;
+------+--------+
| id   | name   |
+------+--------+
| 1    | 张三   |
| 2    | 李四   |
+------+--------+
2 rows in set (0.00 sec)

1.3 外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

语法：

foreign key (字段名) references 主表(列)

没有使用外键(设计下面的结构)

创建表

mysql> create table class (id int primary key comment '班级号',name varchar(12) comment '班级名');
Query OK, 0 rows affected (0.04 sec)mysql> create table student(id int primary key comment '学号', name varchar(15) not null comment '姓名', telphone char(20) unique comment '电话号码', class_id int comment '班级号');
Query OK, 0 rows affected (0.06 sec)mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(12) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(15) | NO   |     | NULL    |       |
| telphone | char(20)    | YES  | UNI | NULL    |       |
| class_id | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

插入数据

class表

mysql> insert into class values(101,'通信1班');
Query OK, 1 row affected (0.01 sec)mysql> insert into class values(102,'通信2班');
Query OK, 1 row affected (0.00 sec)mysql> select * from class ;
+-----+------------+
| id  | name       |
+-----+------------+
| 101 | 通信1班    |
| 102 | 通信2班    |
+-----+------------+
2 rows in set (0.00 sec)

student表

mysql> insert into student values(135792,'张三',123456,101);
Query OK, 1 row affected (0.01 sec)mysql> insert into student values(135795,'李四',654321,102);
Query OK, 1 row affected (0.01 sec)mysql> insert into student values(135786,'王五',654365,102);
Query OK, 1 row affected (0.00 sec)mysql> select * from student;
+--------+--------+----------+----------+
| id     | name   | telphone | class_id |
+--------+--------+----------+----------+
| 135786 | 王五   | 654365   |      101 |
| 135792 | 张三   | 123456   |      102 |
| 135795 | 李四   | 654321   |      102 |
+--------+--------+----------+----------+
3 rows in set (0.00 sec)

删除班级号等于 101 的class表数据

mysql> delete from class where id = 101;
Query OK, 1 row affected (0.00 sec)mysql> select * from class;
+-----+------------+
| id  | name       |
+-----+------------+
| 102 | 通信2班    |
+-----+------------+
1 row in set (0.00 sec)mysql> select * from student;
+--------+--------+----------+----------+
| id     | name   | telphone | class_id |
+--------+--------+----------+----------+
| 135786 | 王五   | 654365   |      102 |
| 135792 | 张三   | 123456   |      101 |
| 135795 | 李四   | 654321   |      102 |
+--------+--------+----------+----------+
3 rows in set (0.00 sec)

班级101还有学生，但是班级却没有了，这合理吗？按照现实来看，这是不合理的，因此我们应该怎么解决这个问题呢？？？

此处可以用到外键。

以 student 为从表，class 为主表建立外键关系。

为了让约束能够正确设置，我们先删除主表的内容，删除从表，并重新创建从表。

删除数据及表结构

mysql> delete from class  where id = 102;
Query OK, 1 row affected (0.01 sec)mysql> drop table student;
Query OK, 0 rows affected (0.02 sec)mysql> select * from class;
Empty set (0.00 sec)

创建从表student

mysql> create table student(id int primary key,-> name varchar(15) not null,-> telphone char(20) unique,-> class_id int,-> foreign key(class_id) references class(id));
Query OK, 0 rows affected (0.09 sec)mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(15) | NO   |     | NULL    |       |
| telphone | char(20)    | YES  | UNI | NULL    |       |
| class_id | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

插入数据

mysql> insert into class values(101,'通信1班');
Query OK, 1 row affected (0.01 sec)mysql> insert into class values(102,'通信2班');
Query OK, 1 row affected (0.00 sec)mysql> select * from class;
+-----+------------+
| id  | name       |
+-----+------------+
| 101 | 通信1班    |
| 102 | 通信2班    |
+-----+------------+
2 rows in set (0.00 sec)mysql> insert into student values(123,'张三',123456,101);
Query OK, 1 row affected (0.01 sec)mysql> insert into student values(124,'李四',654321,102);
Query OK, 1 row affected (0.01 sec)mysql> insert into student values(125,'王五',294321,102);
Query OK, 1 row affected (0.01 sec)mysql> select * from student;
+-----+--------+----------+----------+
| id  | name   | telphone | class_id |
+-----+--------+----------+----------+
| 123 | 张三   | 123456   |      101 |
| 124 | 李四   | 654321   |      102 |
| 125 | 王五   | 294321   |      102 |
+-----+--------+----------+----------+
3 rows in set (0.00 sec)

删除数据

mysql> delete from class where id = 101; # 删除主表内容，因为有外键约束，从表中有数据，因此不能删除
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test3_db`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))mysql> delete from class where id = 102; # 同上，不能删除
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test3_db`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))mysql> delete from student where class_id = 101; # 删除从表数据可以直接删除
Query OK, 1 row affected (0.00 sec)mysql> delete from student where class_id = 102;
Query OK, 2 rows affected (0.00 sec)mysql> select * from student; # 从表为空
Empty set (0.00 sec)mysql> delete from class where id = 101; # 从表为空可以直接删除主表内容
Query OK, 1 row affected (0.00 sec)

1.4 综合案例

有一个商店的数据，记录客户及购物情况，有以下三个表组成：

商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商provider)
客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)
购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)

要求：

每个表的主外键
客户的姓名不能为空值
邮箱不能重复
客户的性别(男，女)

商品表

mysql> create table if not exists goods (-> goods_id int primary key auto_increment comment '商品编号',-> goods_name varchar(32) not null comment '商品名称',-> unitprice float not null default 0.25 comment '商品单价',-> category varchar(12) comment '商品分类',-> provider varchar(40) not null comment '供应商');
Query OK, 0 rows affected (0.04 sec)mysql> desc goods;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| goods_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| goods_name | varchar(32) | NO   |     | NULL    |                |
| unitprice  | float       | NO   |     | 0.25    |                |
| category   | varchar(12) | YES  |     | NULL    |                |
| provider   | varchar(40) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

客户表

mysql> create table if not exists customer ( customer_id int primary key auto_increment comment '客户编号', name varchar(12) not null comment '客户姓名', address varchar(20) comment '客户地址', email varchar(64) unique key comment '电子邮箱', sex enum('男','女') not null comment '客户性别', card_id char(18) unique comment '身份证');
Query OK, 0 rows affected (0.03 sec)mysql> desc customer;
+-------------+-------------------+------+-----+---------+----------------+
| Field       | Type              | Null | Key | Default | Extra          |
+-------------+-------------------+------+-----+---------+----------------+
| customer_id | int(11)           | NO   | PRI | NULL    | auto_increment |
| name        | varchar(12)       | NO   |     | NULL    |                |
| address     | varchar(20)       | YES  |     | NULL    |                |
| email       | varchar(64)       | YES  | UNI | NULL    |                |
| sex         | enum('男','女')   | NO   |     | NULL    |                |
| card_id     | char(18)          | YES  | UNI | NULL    |                |
+-------------+-------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

购买表

mysql> create table if not exists purchase( order_id int primary key auto_increment comment '订单号',  customer_id int comment '客户编号', goods_id int comment '商品编号', num int default 0 comment '购买数量', foreign key(customer_id) references customer(customer_id), foreign key(goods_id) references goods(goods_id));
Query OK, 0 rows affected (0.03 sec)mysql> desc purchase;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| order_id    | int(11) | NO   | PRI | NULL    | auto_increment |
| customer_id | int(11) | YES  | MUL | NULL    |                |
| goods_id    | int(11) | YES  | MUL | NULL    |                |
| num         | int(11) | YES  |     | 0       |                |
+-------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)