MySQL 外鍵約束和表關系相關總結

Posted on by

外鍵(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: 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)

-- 外鍵關聯的數據必須在被關聯表中存在否則會報錯哦~
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=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`))

多對多

多對多的關系以書籍和作者為例進行詳細介紹，為什麼書籍和作者屬於多對多的關系呢？

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

再站在作者表的角度：一個作者是否可以寫多本書？答案是可以

書籍表和作者表是雙向的一對多那麼這兩張表的關系就是多對多。

我們先來創建兩張表：

-- 創建書籍表
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，判斷表與表之間的關系通過換位思考的方式。

一對多表關系：外鍵建在多的一方

一對一表關系：外鍵建在任意一方都可以，推薦建在查詢頻率高的一方

多對多表關系：需要單獨創建第三張表存儲兩張表的關聯關系

以上就是MySQL 外鍵約束和表關系相關總結的詳細內容，更多關於MySQL 外鍵約束和表關系的資料請關註WalkonNet其它相關文章！

推薦閱讀：