MySQL 外鍵約束和表關系相關總結(jié)
外鍵(Foreign Key)
按照上述所說，一張表存儲員工信息會極大的浪費資源，重復數(shù)據(jù)太多，這個問題就類似于將所有的代碼都寫在了一個py文件中，因此我們可以將一個表拆成不同的表，在這不同的表之間建立關聯(lián)，而建立關聯(lián)就需要使用外鍵foreign key。外鍵也屬于約束條件的一種。
如何確定表關系
表與表之間的關系有三種一對多、多對多、一對一。那么如何確定表與表之間的關系呢？
在確定表與表之間的關系時建議換位思考，什么意思呢？就是分別站在兩張表的角度去考慮，比如員工表和部門表的關系：
先站在員工表的角度：員工表中一個員工能否屬于多個部門呢？答案是不能
再站在部門表的角度：部門表中一個部門能否有多個員工呢？答案是可以
因此員工表與部門表是單向的一對多，那么員工表和部門表就是一對多的關系。
如何建立表關系
在建立表關系時，表與表之間的關聯(lián)通常以主鍵id作為關聯(lián)字段。
一對多關系 - 員工表和部門表
在MySQL的關系在沒有多對一的概念，一對多和多對一都是一對多。在創(chuàng)建一對多表關系時需要遵循以下幾點：
第一，外鍵字段建立在多的一方，即員工表
第二，在創(chuàng)建表的時候，一定要先創(chuàng)建被關聯(lián)一方，即部門表
第三，在錄入數(shù)據(jù)的時候也必須先錄入被關聯(lián)表的數(shù)據(jù)，即部門表的數(shù)據(jù)
第四，當不同的表建立關系時，需要進行級聯(lián)更新和刪除也可以稱為同步更新同步刪除，如果不建立級聯(lián)更新和刪除的話，無法對被關聯(lián)表中被關聯(lián)的數(shù)據(jù)進行刪除或者修改id的操作，因為兩張表是相互關聯(lián)的。
-- 創(chuàng)建被關聯(lián)表，部門表
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)
-- 創(chuàng)建外鍵所在的表，員工表
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是外鍵字段，關聯(lián)到bm表中的id字段
on update cascade # 級聯(lián)更新
on delete cascade # 級聯(lián)刪除
);
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)
-- 插入數(shù)據(jù)
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: 0
mysql> 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: 0
mysql> select * from yg;
+----+---------+-------+
| id | yg_name | bm_id |
+----+---------+-------+
| 2 | xu | 1 |
| 3 | zhuang | 2 |
| 4 | lili | 1 |
+----+---------+-------+
3 rows in set (0.00 sec)
-- 外鍵關聯(lián)的數(shù)據(jù)必須在被關聯(lián)表中存在否則會報錯哦~
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`))
-- 如果不使用級聯(lián)更新和刪除的話會出現(xiàn)下面的錯誤，下述的SQL語句后面的文章都會介紹。。。
mysql> update bm set id=5 where id=2; -- 將bm表中id=2的記錄改為id=5
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`))
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`))
多對多
多對多的關系以書籍和作者為例進行詳細介紹，為什么書籍和作者屬于多對多的關系呢？
先站在書籍表的角度：一本書是否可以有多個作者？答案是可以
再站在作者表的角度：一個作者是否可以寫多本書？答案是可以
書籍表和作者表是雙向的一對多那么這兩張表的關系就是多對多。
我們先來創(chuàng)建兩張表：
-- 創(chuàng)建書籍表
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
);
-- 創(chuàng)建作者表
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
);
如果按照上述方式創(chuàng)建表的話肯定是不可能成功的，在創(chuàng)建一對多的表關系時我我們說要先創(chuàng)建被關聯(lián)表，也就是沒有外鍵的表，可是多對多關系是雙向的一對多，每張表中都會有外鍵的存在，怎么辦呢？解決方案就是創(chuàng)建第三張表，這第三張表用來專門存儲多對多關系的兩張表的關聯(lián)。
-- 創(chuàng)建書籍表
mysql> create table book(
id int primary key auto_increment,
name varchar(10),
price int
);
Query OK, 0 rows affected (0.01 sec)
-- 創(chuàng)建作者表
mysql> create table author(
id int primary key auto_increment,
name varchar(6),
age int
);
Query OK, 0 rows affected (0.01 sec)
-- 創(chuàng)建第三章表，存儲book和author表的關聯(lián)關系
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)
一對一
如果一張表的字段特別多，每次查詢數(shù)據(jù)時又不是所有的字段都能用的到，我們就可以將表一一分為二，比如說用戶信息表，用戶的信息包括用戶名 密碼 用戶的年齡 用戶的性別 地址 電話等等，可能經(jīng)常用的只有用戶的用戶名和密碼，這種情況我們就可以將一張用戶信息表拆分成用戶基本信息表和用戶詳細信息表，同樣判斷這兩張表的關系還是通過換位思考：
首先看用戶基本信息表：一個用戶能否有多個詳細信息？答案是不可以；
再看用戶詳情表：一個用戶詳情能否屬于多個用戶？答案是不可以;
單向的一對多都不成立，那么兩者之間的表關系就是一對一或者沒有關系。
使用SQL語句建立一對一的外鍵關系時，外鍵建在任意一方都可以，但是推薦將外鍵建在查詢頻率較高的表中，同樣的，在創(chuàng)建表時還是先創(chuàng)建被關聯(lián)表。
-- 創(chuàng)建用戶詳情表
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
);
表關系總結(jié)
表關系建立需要使用外鍵foreign key，判斷表與表之間的關系通過換位思考的方式。
一對多表關系：外鍵建在多的一方
一對一表關系：外鍵建在任意一方都可以，推薦建在查詢頻率高的一方
多對多表關系：需要單獨創(chuàng)建第三張表存儲兩張表的關聯(lián)關系
以上就是MySQL 外鍵約束和表關系相關總結(jié)的詳細內(nèi)容，更多關于MySQL 外鍵約束和表關系的資料請關注腳本之家其它相關文章！
相關文章
最近遇到了一件非常棘手的問題，用Navicat連接MySQL總是出錯， 網(wǎng)上查閱了一下原因，最終找到解決方案，好吧，下面我就來回憶一下自己怎么處理這問題的，分享到腳本之家平臺需要的朋友參考下吧2021-06-06
這篇文章主要介紹了Mysql配置主從復制-GTID模式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04
本篇文章主要對windows mysql 自動備份的幾種方法進行整理，具有很好的參考價值，需要的朋友一起來看下吧2016-12-12
這篇文章主要介紹了MySQL之常用的MySQL優(yōu)化工具，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-02-02
MySQL 5.6.51 解壓版（zip版）安裝配置圖文方法
這兩天剛試用了一下MySQL5.6.51，感覺還不錯，有兄弟戲稱是一個高富帥版本?，F(xiàn)將MySQL5.6.51 zip解壓版本的安裝配置過程記錄如下，希望能給需要安裝該版本的朋友一點參考作用2015-08-08
本篇文章是對mysql中prompt的用法進行了詳細的分析介紹，需要的朋友參考下2013-06-06
淺談MySQL存儲引擎選擇 InnoDB與MyISAM的優(yōu)缺點分析
MyISAM 是MySQL中默認的存儲引擎，一般來說不是有太多人關心這個東西。決定使用什么樣的存儲引擎是一個很tricky的事情，但是還是值我們?nèi)パ芯恳幌?，這里的文章只考慮 MyISAM 和InnoDB這兩個，因為這兩個是最常見的2013-06-06
這篇文章主要介紹了在MySQL中使用序列的簡單教程,是MySQL入門學習中的基礎知識,文中給出了基于PHP和Perl腳本的示例,需要的朋友可以參考下2015-05-05
隨著項目不斷迭代,使用人數(shù)的不斷增加,數(shù)據(jù)庫中某些表數(shù)據(jù)正在逐步膨脹,往單表千萬迅速靠攏,,所以最近也在考慮做一下分庫分表,本文就給大家詳細講解了什么分庫分表和分庫分表的方案,需要的朋友可以參考下2023-11-11
Mysql更新varchar存儲Json數(shù)據(jù)的操作方法
這篇文章主要介紹了Mysql更新varchar存儲Json數(shù)據(jù)的操作方法,本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧2023-12-12