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