MySQL中的主键，外键有什么作用详解

　　　　　　　　　　　　　　MySQL中的主键，外键有什么作用详解

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

版权声明：原创作品，谢绝转载！否则将追究法律责任。

　　学关系型数据库的同学，尤其在学习主键和外键时会产生一定的困惑。那么今天我们就把这个困惑连根拔起吧。本篇博客不要求你对数据库命令非常熟悉，因为我已经帮大家加了相应的注视。实验环境是关系型数据库 MySQL 8.0.14 。

一.主键

1>.什么是主键

 　　数据库主键，指的是一个列或多列的组合，其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。主键主要是用于其他表的外键关联，以及本记录的修改与删除。

2>.MySQL创建一张没有主键的表

[root@node110 ~]# mysql -uroot -pyinzhengjie
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 13
Server version: 8.0.14 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> 
mysql> 
mysql> CREATE DATABASE devops CHARACTER SET = utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> 
mysql> 
mysql> use devops
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> 
mysql> CREATE TABLE students(stu_id INT(11),stu_name VARCHAR(50),gender INT(11));　　　　　　#首先，我们这里只是创建了一张极为普通的表。
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT INTO students VALUES(1,'jason',10);　　　　　　　　　　　　　　　　　　　　　　　　　　#插入第一条数据
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO students VALUES(2,'danny',20);　　　　　　　　　　　　　　　　　　　　　　　　　　#插入第二条数据
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> INSERT INTO students VALUES(1,'jenny',30);　　　　　　　　　　　　　　　　　　　　　　　　　　 #插入第三条数据，注意！这个id和第一条插入的数据是相同的，别问我为什么这么干，我是故意这样搞的！
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM students;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#我们查询咱们刚刚插入的三条数据
+--------+----------+--------+
| stu_id | stu_name | gender |
+--------+----------+--------+
|      1 | jason    |     10 |
|      2 | danny    |     20 |
|      1 | jenny    |     30 |
+--------+----------+--------+
3 rows in set (0.00 sec)

mysql> 

3>.创建一张含有主键的表（student_primary）

mysql> CREATE TABLE student_primary(stu_id INT(11) PRIMARY KEY AUTO_INCREMENT,stu_name VARCHAR(50),gender INT(11));　　　　　　　　#仔细一下这个建表语句，除了和上面的表名不同，我还为stu_id字段加了主键属性，以及自动增长的属性！
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT INTO student_primary VALUES(1,'json',10); 　　　　　　　　　　　　　　　　#这里我们插入第一条数据
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO student_primary VALUES(2,'danny',20);　　　　　　　　　　　　　　　　#这里我们插入第二条数据
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> INSERT INTO student_primary VALUES(1,'jenny',30);      　　　　　　　　　　　　#这里我们插入第三条数据时，报错啦！提示主键重复！
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> 
mysql> 
mysql> SELECT * FROM student_primary;　　　　　　　　　　　　　　　　　　　　　　　　　　　　#我们查看表中的数据，果不其然，只有两条数据！第三条数据没有被插入进来，因为它不符合我们定义的主键规则！主键必须唯一且非空！
+--------+----------+--------+
| stu_id | stu_name | gender |
+--------+----------+--------+
|      1 | json     |     10 |
|      2 | danny    |     20 |
+--------+----------+--------+
2 rows in set (0.00 sec)

mysql> 

4>.在创建一张含有主键的表（course）

mysql> CREATE TABLE course(id INT(11) PRIMARY KEY AUTO_INCREMENT,course_name VARCHAR(30));　　　　　　　　　　
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> INSERT INTO course VALUES(1,'Chinese'); 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#插入第一条数据
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> INSERT INTO course VALUES(2,'English'); 
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO course VALUES(3,'Mathematics'),(4,'Physics'),(5,'Chemistry'),(6,'Biology');　　　　　　　　#咱们可以同时插入多条数据
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT * FROM course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Chinese     |
|  2 | English     |
|  3 | Mathematics |
|  4 | Physics     |
|  5 | Chemistry   |
|  6 | Biology     |
+----+-------------+
6 rows in set (0.00 sec)

mysql> 

二.外键

1>.什么是外键

　　如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

2>.结合之前创建的含有主键的表（student_primary和course），创建一个外键表

mysql> SHOW TABLES;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#这是我们之前创建的三张表
+------------------+
| Tables_in_devops |
+------------------+
| course           |
| student_primary  |
| students         |
+------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> DESC course;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#查看course表的结构
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| course_name | varchar(30) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> 
mysql> DESC student_primary;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#查看student_primary表的结构
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| stu_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| stu_name | varchar(50) | YES  |     | NULL    |                |
| gender   | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> 
mysql> CREATE TABLE student_course_foreign(scf_id INT(11),course_id INT(11),CONSTRAINT waijian_01 FOREIGN KEY(scf_id) REFERENCES student_primary(stu_id),CONSTRAINT waijian_02 FOREIGN KEY(course_id) REFERENCES course(id));               #创建一张外键表，这个表分的scf_id字段外键关联了student_primary的stu_id字段，该表的course_id字段外键关联了course表的id字段。
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> DESC student_course_foreign;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| scf_id    | int(11) | YES  | MUL | NULL    |       |
| course_id | int(11) | YES  | MUL | NULL    |       |
+-----------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> 
mysql> 
mysql> SELECT * FROM student_primary;　　　　　　　　　　#注意该表的主键stu_id字段，同时该字段是被student_course_foreign表的scf_id字段进行了外键关联。
+--------+----------+--------+
| stu_id | stu_name | gender |
+--------+----------+--------+
|      1 | json     |     10 |
|      2 | danny    |     20 |
+--------+----------+--------+
2 rows in set (0.01 sec)

mysql> 
mysql> SELECT * FROM course;　　　　　　　　　　　　　　#注意该表的主键id字段，同时该字段是被student_course_foreign表的course_id字段进行了外间关联
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Chinese     |
|  2 | English     |
|  3 | Mathematics |
|  4 | Physics     |
|  5 | Chemistry   |
|  6 | Biology     |
+----+-------------+
6 rows in set (0.00 sec)

mysql> 
mysql> INSERT INTO student_course_foreign VALUES(1,2);　　　　#我们往表中插入第一条记录，发现成功了，注意这个1和2大家是否在上面的两张表那里见过？
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> INSERT INTO student_course_foreign VALUES(2,3);　　　　#插入第二条记录
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO student_course_foreign VALUES(2,6);　　　　#插入第三天记录，我们发现scf_id由于没有被设置为主键，因此，改字段咱们是可以重复的！
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO student_course_foreign VALUES(2,10);　　　#大家看这里，报错啦！原因何在？这是因为我们插入的两个数字中，第一个数字 “2” 在外键关联的studnets_primary表中的stu_id是存在的！而第二个数字“10”在外键关联的course表中id字段是不存在的！这是报错的根本！
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`devops`.`student_course_foreign`, CONSTRAINT `waijian_02` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`))
mysql> 
mysql> SELECT * FROM student_course_foreign;　　　　　　　　　　#此时，我们查看到外键关联的表，发现记录信息我们只成功插入了3条数据！
+--------+-----------+
| scf_id | course_id |
+--------+-----------+
|      1 |         2 |
|      2 |         3 |
|      2 |         6 |
+--------+-----------+
3 rows in set (0.00 sec)

mysql>  

3>.删除外键表关联的主键表

mysql> SELECT * FROM student_primary;　　　　　　　　　　　　　　　　　　#在做操作之前，我们先看一下student_primary表中的数据。
+--------+----------+--------+
| stu_id | stu_name | gender |
+--------+----------+--------+
|      1 | json     |     10 |
|      2 | danny    |     20 |
+--------+----------+--------+
2 rows in set (0.00 sec)

mysql> 
mysql> SELECT * FROM student_course_foreign;　　　　　　　　　　　　　#插卡student_course_foreign表中的数据。
+--------+-----------+
| scf_id | course_id |
+--------+-----------+
|      1 |         2 |
|      2 |         3 |
|      2 |         6 |
+--------+-----------+
3 rows in set (0.01 sec)

mysql> 
mysql> DELETE FROM student_primary WHERE stu_id = 2;  　　　　　　　　　　　　　　#删除主表中的数据，发现不让删除啦！别慌，看提示说，我们创建外键关联了，需要先更新外键表的信息绑定的关系，才能删除主表中的数据！
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`devops`.`student_course_foreign`, CONSTRAINT `waijian_01` FOREIGN KEY (`scf_id`) REFERENCES `student_primary` (`stu_id`))
mysql> 
mysql> DELETE FROM student_course_foreign  WHERE scf_id = 2;　　　　　　　　　　　　#既然这样，我们就把关联的外键表中对应的数据删除掉！
Query OK, 2 rows affected (0.00 sec)

mysql> 
mysql> DELETE FROM student_primary where stu_id = 2;        　　　　　　　　　　　#将外键表的绑定关系的数据删除掉后，我们再来删除主表中的数据，发现没有提示报错啦～
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM student_primary;　　　　　　　　　　　　　　　　　　　　　　　　#查看主表中的数据
+--------+----------+--------+
| stu_id | stu_name | gender |
+--------+----------+--------+
|      1 | json     |     10 |
+--------+----------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM student_course_foreign;　　　　　　　　　　　　　　　　　　　　#查看外键关联的表数据信息
+--------+-----------+
| scf_id | course_id |
+--------+-----------+
|      1 |         2 |
+--------+-----------+
1 row in set (0.00 sec)

mysql> 

三.总结主键和外键的作用

 　　大家看过上面两个案例的小伙伴，估计对主键和外键在作用上应该问题不大啦～

1>.主键的作用

　　主键是能确定一条记录的唯一标识，主键字段必须唯一，必须非空，一个表中只能有一个主键，主键可以包含一个或多个字段。

　　打个比方，一条记录包括身份正号，姓名，年龄，学校，国籍，性别等。身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。 

2>.外键的作用

　　外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。外键用于与另一张表的关联。是能确定另一张表记录的字段，保持数据的一致性、完整性。