MariaDB資料庫的外來鍵約束例項程式碼介紹詳解

　　文章主要給大家介紹了關於MariaDB資料庫的外來鍵約束的相關資料，文中透過示例程式碼介紹的非常詳細，需要的朋友可以參考借鑑，下面隨著小編來一起學習學習吧

外來鍵

外來鍵的用途是確保資料的完整性。它通常包括以下幾種：

　　1 實體完整性，確保每個實體是唯一的(透過主鍵來實施)

　　2 域完整性，確保屬性值只從一套特定可選的集合裡選擇

　　3 關聯完整性，確保每個外來鍵或是NULL(如果允許的話)或含有與相關主鍵值相配的值

1.什麼是外來鍵約束

　　與主鍵約束不同，建立外來鍵約束不會自動建立對應的索引。 但是由於以下原因，對外來鍵手動建立索引通常是有用的：

　　當在查詢中組合相關表中的資料時，經常在聯接條件中使用外來鍵列，方法是將一個表的外來鍵約束中的一列或多列與另一個表中的主鍵列或唯一鍵列匹配。 索引使 資料庫引擎 可以在外來鍵表中快速查詢相關資料。 但是，建立此索引並不是必需的。 即使沒有對兩個相關表定義主鍵或外來鍵約束，也可以對來自這兩個表中的資料進行組合，但兩個表間的外來鍵關係說明已用其鍵作為條件對其進行了最佳化，以便組合到查詢中。

　　對主鍵約束的更改可由相關表中的外來鍵約束檢查。

　　外來鍵約束(foreign key)就是表與表之間的某種約定的關係，由於這種關係的存在，我們能夠讓表與表之間的資料，更加的完整，關連性更強。

　　關於資料表的完整性和關連性，可以舉個例子

　　有二張表，一張是使用者表，一張是訂單表：

　　1.如果我刪除了使用者表裡的使用者，那麼訂單表裡面跟這個使用者有關的資料，就成了無頭資料了，不完整了。

　　2.如果我在訂單表裡面，隨便插入了一條資料，這個訂單在使用者表裡面，沒有與之對應的使用者。這樣資料也不完整了。

　　如果有外來鍵的話，就方便多了，可以不讓使用者刪除資料，或者刪除使用者的話，透過外來鍵同樣刪除訂單表裡面的資料，這樣也能讓資料完整。

　　透過外來鍵約束，每次插入或更新資料表時，都會檢查資料的完整性。

2.建立外來鍵約束

2.1 方法一：透過create table建立外來鍵

　　語法：

1. create table 資料表名稱(
2. ...,
3. [CONSTRAINT [約束名稱]] FOREIGN KEY [外來鍵欄位]
4. REFERENCES [外來鍵表名](外來鍵欄位，外來鍵欄位2…..)
5. [ON DELETE CASCADE ]
6. [ON UPDATE CASCADE ]
7. )

　　引數的解釋：

　　RESTRICT: 拒絕對父表的刪除或更新操作。

　　CASCADE: 從父表刪除或更新且自動刪除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用

注意：on update cascade是級聯更新的意思，on delete cascade是級聯刪除的意思，意思就是說當你更新或刪除主鍵表，那外來鍵表也會跟隨一起更新或刪除。

　　精簡化後的語法：

1. foreign key 當前表的欄位 references 外部表名 (關聯的欄位） type=innodb

2.1.1 插入測試資料

　　例子：我們建立一個資料庫，包含使用者資訊表和訂單表

1. MariaDB [book]> create database market; # 建立market資料庫
2. Query OK, 1 row affected (0.00 sec)
3.  
4. MariaDB [book]> use market; # 使用market資料庫
5. Database changed
6.  
7. MariaDB [market]> create table userprofile(id int(11) not null auto_increment, name varchar(50) not null default '', sex int(1) not null default '0', primary key(id))ENGINE=innodb; # 建立userprofile資料表，指定使用innodb引擎
8. Query OK, 0 rows affected (0.07 sec)
9.  
10. MariaDB [market]> create table user_order(o_id int(11) auto_increment, u_id int(11) default '0', username varchar(50), money int(11), primary key(o_id), index(u_id), foreign key order_f_key(u_id) references userprofile(id) on delete cascade on update cascade); # 建立user_order資料表，同時為user_order表的u_id欄位做外來鍵約束，繫結userprofile表的id欄位
11. Query OK, 0 rows affected (0.04 sec)
12.  
13. MariaDB [market]> insert into userprofile(name,sex)values('HA',1),('LB',2),('HPC',1); # 向userprofile資料表插入三條記錄
14. Query OK, 3 rows affected (0.01 sec)
15. Records: 3 Duplicates: 0 Warnings: 0
16.  
17. MariaDB [market]> select * from userprofile; # 查詢userprofile資料表的所有記錄
18. +----+------+-----+
19. | id | name | sex |
20. +----+------+-----+
21. | 1 | HA | 1 |
22. | 2 | LB | 2 |
23. | 3 | HPC | 1 |
24. +----+------+-----+
25. 3 rows in set (0.00 sec)
26.  
27. MariaDB [market]> insert into user_order(u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256); # 向user_order資料表插入三條記錄
28. Query OK, 3 rows affected (0.02 sec)
29. Records: 3 Duplicates: 0 Warnings: 0
30.  
31. MariaDB [market]> select * from user_order; # 查詢user_order資料表的所有記錄
32. +------+------+----------+-------+
33. | o_id | u_id | username | money |
34. +------+------+----------+-------+
35. | 1 | 1 | HA | 234 |
36. | 2 | 2 | LB | 146 |
37. | 3 | 3 | HPC | 256 |
38. +------+------+----------+-------+
39. 3 rows in set (0.00 sec)
40.  
41. MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_order where id=u_id; # 聯表查詢
42. +----+------+-----+-------+------+
43. | id | name | sex | money | o_id |
44. +----+------+-----+-------+------+
45. | 1 | HA | 1 | 234 | 1 |
46. | 2 | LB | 2 | 146 | 2 |
47. | 3 | HPC | 1 | 256 | 3 |
48. +----+------+-----+-------+------+
49. 3 rows in set (0.03 sec)

2.1.2 測試級聯刪除

1. MariaDB [market]> delete from userprofile where id=1; # 刪除user表中id為1的資料
2. Query OK, 1 row affected (0.01 sec)
3.  
4. MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_order where id=u_id;
5. +----+------+-----+-------+------+
6. | id | name | sex | money | o_id |
7. +----+------+-----+-------+------+
8. | 2 | LB | 2 | 146 | 2 |
9. | 3 | HPC | 1 | 256 | 3 |
10. +----+------+-----+-------+------+
11. 2 rows in set (0.00 sec)
12.  
13. MariaDB [market]> select * from user_order; # 檢視order表的資料
14. +------+------+----------+-------+
15. | o_id | u_id | username | money |
16. +------+------+----------+-------+
17. | 2 | 2 | LB | 146 |
18. | 3 | 3 | HPC | 256 |
19. +------+------+----------+-------+
20. 3 rows in set (0.00 sec)

2.1.3 測試級聯更新

　　更新資料之前的狀態

1. MariaDB [market]> select * from userprofile; # 檢視userprofile表的資料
2. +----+------+-----+
3. | id | name | sex |
4. +----+------+-----+
5. | 2 | LB | 2 |
6. | 3 | HPC | 1 |
7. +----+------+-----+
8. 3 rows in set (0.00 sec)
9.  
10. MariaDB [market]> select * from user_order; # 檢視order表的資料
11. +------+------+----------+-------+
12. | o_id | u_id | username | money |
13. +------+------+----------+-------+
14. | 2 | 2 | LB | 146 |
15. | 3 | 3 | HPC | 256 |
16. +------+------+----------+-------+
17. 3 rows in set (0.00 sec)

　　更新資料?

1. MariaDB [market]> update userprofile set id=6 where id=2; # 把userprofile資料表中id為2的使用者改為id為6
2. Query OK, 1 row affected (0.02 sec)
3. Rows matched: 1 Changed: 1 Warnings: 0

　　更新資料後的狀態?

1. MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_order where id=u_id; # 聯表查詢，可以看出表中已經沒有id為2的使用者了
2. +----+------+-----+-------+------+
3. | id | name | sex | money | o_id |
4. +----+------+-----+-------+------+
5. | 6 | LB | 2 | 146 | 2 |
6. | 3 | HPC | 1 | 256 | 3 |
7. +----+------+-----+-------+------+
8. 2 rows in set (0.00 sec)
9.  
10. MariaDB [market]> select * from userprofile; # 檢視userprofile表的資料，id只剩下3和6
11. +----+------+-----+
12. | id | name | sex |
13. +----+------+-----+
14. | 3 | HPC | 1 |
15. | 6 | LB | 2 |
16. +----+------+-----+
17. 2 rows in set (0.00 sec)
18.  
19. MariaDB [market]> select * from user_order; # 檢視user_order表的資料,u_id也改為6
20. +------+------+----------+-------+
21. | o_id | u_id | username | money |
22. +------+------+----------+-------+
23. | 2 | 6 | LB | 146 |
24. | 3 | 3 | HPC | 256 |
25. +------+------+----------+-------+
26. 2 rows in set (0.00 sec)

2.1.4 測試資料完整性

1. MariaDB [market]> insert into user_order(u_id,username,money)values(5,"XJ",345); # 單獨向user_order資料表中插入資料，插入資料失敗
2. ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`market`.`user_order`, CONSTRAINT `user_order_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userprofile` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

　　在上面的例子中，user_order表的外來鍵約束，user_order表受userprofile表的約束

　　在user_order裡面插入一條資料u_id為5使用者，在userprofile表裡面根本沒有，所以插入資料失敗

　　先向userprofile表中插入記錄，再向user_order表中插入記錄就可以了

1. MariaDB [market]> insert into userprofile values(5,"XJ",1); # 先向userprofile資料表中插入id為5的記錄，插入資料成功
2. Query OK, 1 row affected (0.01 sec)
3.  
4. MariaDB [market]> insert into user_order(u_id,username,money) values(5,"XJ",345); # 再向user_order資料表中插入資料,成功
5. Query OK, 1 row affected (0.00 sec)
6.  
7. MariaDB [market]> select * from userprofile; # 查詢userprofile資料表中的所有記錄
8. +----+------+-----+
9. | id | name | sex |
10. +----+------+-----+
11. | 3 | HPC | 1 |
12. | 5 | XJ | 1 |
13. | 6 | LB | 2 |
14. +----+------+-----+
15. 3 rows in set (0.00 sec)
16.  
17. MariaDB [market]> select * from user_order; # 查詢user_order資料表中的所有記錄
18. +------+------+----------+-------+
19. | o_id | u_id | username | money |
20. +------+------+----------+-------+
21. | 2 | 6 | LB | 146 |
22. | 3 | 3 | HPC | 256 |
23. | 5 | 5 | XJ | 345 |
24. +------+------+----------+-------+
25. 3 rows in set (0.01 sec)

2.2 方法二：透過alter table建立外來鍵和級聯更新，級聯刪除

　　語法：

1. alter table 資料表名稱 add
2. [constraint [約束名稱] ] foreign key (外來鍵欄位,..) references 資料表(參照欄位,...)
3. [on update cascade|set null|no action]
4. [on delete cascade|set null|no action]
5. )

　　例子：

1. MariaDB [market]> create table user_order1(o_id int(11) auto_increment,u_id int(11) default "0",username varchar(50),money int(11),primary key(o_id),index(u_id)); # 建立user_order1資料表，建立表時不使用外來鍵約束
2. Query OK, 0 rows affected (0.11 sec)
3.  
4. MariaDB [market]> show create table user_order1; # 檢視user_order1資料表的建立資訊，沒有外來鍵約束
5. +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6. | Table | Create Table |
7. +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8. | user_order1 | CREATE TABLE `user_order1` (
9. `o_id` int(11) NOT NULL AUTO_INCREMENT,
10. `u_id` int(11) DEFAULT '0',
11. `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
12. `money` int(11) DEFAULT NULL,
13. PRIMARY KEY (`o_id`),
14. KEY `u_id` (`u_id`)
15. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
16. +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
17. 1 row in set (0.01 sec)
18.  
19. MariaDB [market]> alter table user_order1 add foreign key(u_id) references userprofile(id) on delete cascade on update cascade; # 使用alter修改user_order1資料表，為user_order1資料表新增外來鍵約束
20. Query OK, 0 rows affected (0.05 sec)
21. Records: 0 Duplicates: 0 Warnings: 0
22.  
23. MariaDB [market]> show create table user_order1; # 檢視user_order1資料表的建立資訊，已經新增了外來鍵約束
24. +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
25. | Table | Create Table |
26. +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
27. | user_order1 | CREATE TABLE `user_order1` (
28. `o_id` int(11) NOT NULL AUTO_INCREMENT,
29. `u_id` int(11) DEFAULT '0',
30. `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
31. `money` int(11) DEFAULT NULL,
32. PRIMARY KEY (`o_id`),
33. KEY `u_id` (`u_id`),
34. CONSTRAINT `user_order1_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userprofile` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
35. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
36. +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
37. 1 row in set (0.00 sec)

3.刪除外來鍵

　　語法

1. alter table 資料表名稱 drop foreign key 約束（外來鍵）名稱

　　例子：

1. MariaDB [market]> show create table user_order1; # 檢視user_order1資料表的建立資訊，包含外來鍵約束
2. +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3. | Table | Create Table |
4. +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5. | user_order1 | CREATE TABLE `user_order1` (
6. `o_id` int(11) NOT NULL AUTO_INCREMENT,
7. `u_id` int(11) DEFAULT '0',
8. `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
9. `money` int(11) DEFAULT NULL,
10. PRIMARY KEY (`o_id`),
11. KEY `u_id` (`u_id`),
12. CONSTRAINT `user_order1_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userprofile` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
13. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
14. +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15. 1 row in set (0.00 sec)
16.  
17. MariaDB [market]> alter table user_order1 drop foreign key user_order1_ibfk_1; # 為user_order1資料表刪除外來鍵約束，外來鍵名稱必須與從`show create table user_order1`語句中查到的相同
18. Query OK, 0 rows affected (0.05 sec)
19. Records: 0 Duplicates: 0 Warnings: 0
20.  
21. MariaDB [market]> show create table user_order1; # 檢視user_order1資料表的建立資訊，外來鍵約束已經被刪除了
22. +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
23. | Table | Create Table |
24. +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
25. | user_order1 | CREATE TABLE `user_order1` (
26. `o_id` int(11) NOT NULL AUTO_INCREMENT,
27. `u_id` int(11) DEFAULT '0',
28. `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
29. `money` int(11) DEFAULT NULL,
30. PRIMARY KEY (`o_id`),
31. KEY `u_id` (`u_id`)
32. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
33. +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
34. 1 row in set (0.00 sec)

4.使用外來鍵約束的條件

　　要想外來鍵建立成功，必須滿足以下4個條件：

　　1、確保參照的表和欄位存在。

　　2、組成外來鍵的欄位被索引。

　　3、必須使用type指定儲存引擎為：innodb.

　　4、外來鍵欄位和關聯欄位，資料型別必須一致。

5.使用外來鍵約束需要的注意事項

　　1.on delete cascade on update cascade 新增級聯刪除和更新：

　　2.確保參照的表userprofile中id欄位存在。

　　3.確保組成外來鍵的欄位u_id被索引

　　4.必須使用type指定儲存引擎為：innodb。

　　5.外來鍵欄位和關聯欄位，資料型別必須一致。

總結

　　以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值。