MySQL 外键约束和表关系相关总结

外键(Foreign Key)

按照上述所说，一张表存储员工信息会极大的浪费资源，重复数据太多，这个问题就类似于将所有的代码都写在了一个py文件中，因此我们可以将一个表拆成不同的表，在这不同的表之间建立关联，而建立关联就需要使用外键foreign key。外键也属于约束条件的一种。

如何确定表关系

表与表之间的关系有三种一对多、多对多、一对一。那么如何确定表与表之间的关系呢？

在确定表与表之间的关系时建议换位思考，什么意思呢？就是分别站在两张表的角度去考虑，比如员工表和部门表的关系：

先站在员工表的角度：员工表中一个员工能否属于多个部门呢？答案是不能

再站在部门表的角度：部门表中一个部门能否有多个员工呢？答案是可以

因此员工表与部门表是单向的一对多，那么员工表和部门表就是一对多的关系。

如何建立表关系

在建立表关系时，表与表之间的关联通常以主键id作为关联字段。

一对多关系 - 员工表和部门表

在MySQL的关系在没有多对一的概念，一对多和多对一都是一对多。在创建一对多表关系时需要遵循以下几点：

第一，外键字段建立在多的一方，即员工表

第二，在创建表的时候，一定要先创建被关联一方，即部门表

第三，在录入数据的时候也必须先录入被关联表的数据，即部门表的数据

第四，当不同的表建立关系时，需要进行级联更新和删除也可以称为同步更新同步删除，如果不建立级联更新和删除的话，无法对被关联表中被关联的数据进行删除或者修改id的操作，因为两张表是相互关联的。

-- 创建被关联表，部门表mysql> create table bm(    id int primary key auto_increment,     bm_name varchar(10),     bm_desc char(64));Query OK, 0 rows affected (0.01 sec)mysql> desc bm;+---------+-------------+------+-----+---------+----------------+| Field   | Type        | Null | Key | Default | Extra          |+---------+-------------+------+-----+---------+----------------+| id      | int(11)     | NO   | PRI | NULL    | auto_increment || bm_name | varchar(10) | YES  |     | NULL    |                || bm_desc | char(64)    | YES  |     | NULL    |                |+---------+-------------+------+-----+---------+----------------+3 rows in set (0.01 sec)-- 创建外键所在的表，员工表mysql> create table yg(    id int primary key auto_increment,     yg_name varchar(6),     bm_id int,     foreign key(bm_id) references bm(id)  -- 表示bm_id是外键字段，关联到bm表中的id字段    on update cascade  # 级联更新    on delete cascade  # 级联删除);Query OK, 0 rows affected (0.10 sec)mysql> desc yg;+---------+------------+------+-----+---------+----------------+| Field   | Type       | Null | Key | Default | Extra          |+---------+------------+------+-----+---------+----------------+| id      | int(11)    | NO   | PRI | NULL    | auto_increment || yg_name | varchar(6) | YES  |     | NULL    |                || bm_id   | int(11)    | YES  | MUL | NULL    |                |+---------+------------+------+-----+---------+----------------+3 rows in set (0.01 sec)-- 插入数据mysql> insert into bm (bm_name, bm_desc) values ('python', '人生苦短'),('go', 'let us go');Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from bm;+----+---------+--------------+| id | bm_name | bm_desc      |+----+---------+--------------+|  1 | python  | 人生苦短     ||  2 | go      | let us go    |+----+---------+--------------+2 rows in set (0.00 sec)mysql> insert into yg (yg_name, bm_id) values ('xu', 1), ('zhuang', 2), ('lili', 1);Query OK, 3 rows affected (0.09 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from yg;+----+---------+-------+| id | yg_name | bm_id |+----+---------+-------+|  2 | xu      |     1 ||  3 | zhuang  |     2 ||  4 | lili    |     1 |+----+---------+-------+3 rows in set (0.00 sec)-- 外键关联的数据必须在被关联表中存在否则会报错哦~mysql> insert into yg (yg_name, bm_id) values ('xu', 3);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`))-- 如果不使用级联更新和删除的话会出现下面的错误，下述的SQL语句后面的文章都会介绍。。。mysql> update bm set id=5 where id=2;  -- 将bm表中id=2的记录改为id=5ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`))mysql> delete from bm where id =2;  -- 删除bm表中id为2的那条记录ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`book_manage`.`yg`, CONSTRAINT `yg_ibfk_1` FOREIGN KEY (`bm_id`) REFERENCES `bm` (`id`))

先站在书籍表的角度：一本书是否可以有多个作者？答案是可以

再站在作者表的角度：一个作者是否可以写多本书？答案是可以

书籍表和作者表是双向的一对多那么这两张表的关系就是多对多。

我们先来创建两张表：

-- 创建书籍表create table book(    id int primary key auto_increment,    title varchar(32),    price int,    author_id int,    foreign key(author_id) references author(id)    on update cascade    on delete cascade);-- 创建作者表create table author(    id int primary key auto_increment,    name varchar(32),    age int,    book_id int,    foreign key(book_id) references book(id)    on update cascade    on delete cascade);

-- 创建书籍表mysql> create table book(    id int primary key auto_increment,     name varchar(10),     price int);Query OK, 0 rows affected (0.01 sec)-- 创建作者表mysql> create table author(    id int primary key auto_increment,     name varchar(6),     age int);Query OK, 0 rows affected (0.01 sec)-- 创建第三章表，存储book和author表的关联关系mysql> create table book2author(    id int primary key auto_increment,     author_id int,     book_id int,     foreign key(author_id) references author(id)     on update cascade     on delete cascade,     foreign key(book_id) references book(id)     on update cascade     on delete cascade);Query OK, 0 rows affected (0.02 sec)

首先看用户基本信息表：一个用户能否有多个详细信息？答案是不可以；

再看用户详情表：一个用户详情能否属于多个用户？答案是不可以;

单向的一对多都不成立，那么两者之间的表关系就是一对一或者没有关系。

使用SQL语句建立一对一的外键关系时，外键建在任意一方都可以，但是推荐将外键建在查询频率较高的表中，同样的，在创建表时还是先创建被关联表。

-- 创建用户详情表create table authordetail(id int primary key auto_increment,phone int,addr varchar(64));-- 用户基本信息表create table author(id int primary key auto_increment,    name varchar(32),    age int,    authordetali_id int,    foreign key(authordetali_id) references authordetali(id)    on update cascade    on delete cascade);

一对多表关系：外键建在多的一方

一对一表关系：外键建在任意一方都可以，推荐建在查询频率高的一方

多对多表关系：需要单独创建第三张表存储两张表的关联关系

外键(Foreign Key)

如何确定表关系

如何建立表关系

一对多关系 - 员工表和部门表

更多相关文章

随机推荐