MySQL 外鍵約束和表關(guān)系相關(guān)總結(jié)
外鍵(Foreign Key)
按照上述所說(shuō)，一張表存儲(chǔ)員工信息會(huì)極大的浪費(fèi)資源，重復(fù)數(shù)據(jù)太多，這個(gè)問(wèn)題就類(lèi)似于將所有的代碼都寫(xiě)在了一個(gè)py文件中，因此我們可以將一個(gè)表拆成不同的表，在這不同的表之間建立關(guān)聯(lián)，而建立關(guān)聯(lián)就需要使用外鍵foreign key。外鍵也屬于約束條件的一種。
如何確定表關(guān)系
表與表之間的關(guān)系有三種一對(duì)多、多對(duì)多、一對(duì)一。那么如何確定表與表之間的關(guān)系呢？
在確定表與表之間的關(guān)系時(shí)建議換位思考，什么意思呢？就是分別站在兩張表的角度去考慮，比如員工表和部門(mén)表的關(guān)系：
先站在員工表的角度：?jiǎn)T工表中一個(gè)員工能否屬于多個(gè)部門(mén)呢？答案是不能
再站在部門(mén)表的角度：部門(mén)表中一個(gè)部門(mén)能否有多個(gè)員工呢？答案是可以
因此員工表與部門(mén)表是單向的一對(duì)多，那么員工表和部門(mén)表就是一對(duì)多的關(guān)系。
如何建立表關(guān)系
在建立表關(guān)系時(shí)，表與表之間的關(guān)聯(lián)通常以主鍵id作為關(guān)聯(lián)字段。
一對(duì)多關(guān)系 - 員工表和部門(mén)表
在MySQL的關(guān)系在沒(méi)有多對(duì)一的概念，一對(duì)多和多對(duì)一都是一對(duì)多。在創(chuàng)建一對(duì)多表關(guān)系時(shí)需要遵循以下幾點(diǎn)：
第一，外鍵字段建立在多的一方，即員工表
第二，在創(chuàng)建表的時(shí)候，一定要先創(chuàng)建被關(guān)聯(lián)一方，即部門(mén)表
第三，在錄入數(shù)據(jù)的時(shí)候也必須先錄入被關(guān)聯(lián)表的數(shù)據(jù)，即部門(mén)表的數(shù)據(jù)
第四，當(dāng)不同的表建立關(guān)系時(shí)，需要進(jìn)行級(jí)聯(lián)更新和刪除也可以稱(chēng)為同步更新同步刪除，如果不建立級(jí)聯(lián)更新和刪除的話(huà)，無(wú)法對(duì)被關(guān)聯(lián)表中被關(guān)聯(lián)的數(shù)據(jù)進(jìn)行刪除或者修改id的操作，因?yàn)閮蓮埍硎窍嗷リP(guān)聯(lián)的。
-- 創(chuàng)建被關(guān)聯(lián)表，部門(mé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是外鍵字段，關(guān)聯(lián)到bm表中的id字段
on update cascade # 級(jí)聯(lián)更新
on delete cascade # 級(jí)聯(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)
-- 外鍵關(guān)聯(lián)的數(shù)據(jù)必須在被關(guān)聯(lián)表中存在否則會(huì)報(bào)錯(cuò)哦~
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`))
-- 如果不使用級(jí)聯(lián)更新和刪除的話(huà)會(huì)出現(xiàn)下面的錯(cuò)誤，下述的SQL語(yǔ)句后面的文章都會(huì)介紹。。。
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`))
多對(duì)多
多對(duì)多的關(guān)系以書(shū)籍和作者為例進(jìn)行詳細(xì)介紹，為什么書(shū)籍和作者屬于多對(duì)多的關(guān)系呢？
先站在書(shū)籍表的角度：一本書(shū)是否可以有多個(gè)作者？答案是可以
再站在作者表的角度：一個(gè)作者是否可以寫(xiě)多本書(shū)？答案是可以
書(shū)籍表和作者表是雙向的一對(duì)多那么這兩張表的關(guān)系就是多對(duì)多。
我們先來(lái)創(chuàng)建兩張表：
-- 創(chuàng)建書(shū)籍表
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)建表的話(huà)肯定是不可能成功的，在創(chuàng)建一對(duì)多的表關(guān)系時(shí)我我們說(shuō)要先創(chuàng)建被關(guān)聯(lián)表，也就是沒(méi)有外鍵的表，可是多對(duì)多關(guān)系是雙向的一對(duì)多，每張表中都會(huì)有外鍵的存在，怎么辦呢？解決方案就是創(chuàng)建第三張表，這第三張表用來(lái)專(zhuān)門(mén)存儲(chǔ)多對(duì)多關(guān)系的兩張表的關(guān)聯(lián)。
-- 創(chuàng)建書(shū)籍表
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)建第三章表，存儲(chǔ)book和author表的關(guān)聯(lián)關(guā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)
一對(duì)一
如果一張表的字段特別多，每次查詢(xún)數(shù)據(jù)時(shí)又不是所有的字段都能用的到，我們就可以將表一一分為二，比如說(shuō)用戶(hù)信息表，用戶(hù)的信息包括用戶(hù)名 密碼 用戶(hù)的年齡 用戶(hù)的性別 地址 電話(huà)等等，可能經(jīng)常用的只有用戶(hù)的用戶(hù)名和密碼，這種情況我們就可以將一張用戶(hù)信息表拆分成用戶(hù)基本信息表和用戶(hù)詳細(xì)信息表，同樣判斷這兩張表的關(guān)系還是通過(guò)換位思考：
首先看用戶(hù)基本信息表：一個(gè)用戶(hù)能否有多個(gè)詳細(xì)信息？答案是不可以；
再看用戶(hù)詳情表：一個(gè)用戶(hù)詳情能否屬于多個(gè)用戶(hù)？答案是不可以;
單向的一對(duì)多都不成立，那么兩者之間的表關(guān)系就是一對(duì)一或者沒(méi)有關(guān)系。
使用SQL語(yǔ)句建立一對(duì)一的外鍵關(guān)系時(shí)，外鍵建在任意一方都可以，但是推薦將外鍵建在查詢(xún)頻率較高的表中，同樣的，在創(chuàng)建表時(shí)還是先創(chuàng)建被關(guān)聯(lián)表。
-- 創(chuàng)建用戶(hù)詳情表
create table authordetail(
id int primary key auto_increment,
phone int,
addr varchar(64)
);
-- 用戶(hù)基本信息表
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
);
表關(guān)系總結(jié)
表關(guān)系建立需要使用外鍵foreign key，判斷表與表之間的關(guān)系通過(guò)換位思考的方式。
一對(duì)多表關(guān)系：外鍵建在多的一方
一對(duì)一表關(guān)系：外鍵建在任意一方都可以，推薦建在查詢(xún)頻率高的一方
多對(duì)多表關(guān)系：需要單獨(dú)創(chuàng)建第三張表存儲(chǔ)兩張表的關(guān)聯(lián)關(guān)系
以上就是MySQL 外鍵約束和表關(guān)系相關(guān)總結(jié)的詳細(xì)內(nèi)容，更多關(guān)于MySQL 外鍵約束和表關(guān)系的資料請(qǐng)關(guān)注本站其它相關(guān)文章！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信