文章主要給大家介紹了關(guān)于MariaDB數(shù)據(jù)庫(kù)的外鍵約束的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，需要的朋友可以參考借鑒，下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

外鍵

外鍵的用途是確保數(shù)據(jù)的完整性。它通常包括以下幾種：

　　1 實(shí)體完整性，確保每個(gè)實(shí)體是唯一的(通過(guò)主鍵來(lái)實(shí)施)

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

　　3 關(guān)聯(lián)完整性，確保每個(gè)外鍵或是NULL(如果允許的話)或含有與相關(guān)主鍵值相配的值

1.什么是外鍵約束

　　與主鍵約束不同，創(chuàng)建外鍵約束不會(huì)自動(dòng)創(chuàng)建對(duì)應(yīng)的索引。 但是由于以下原因，對(duì)外鍵手動(dòng)創(chuàng)建索引通常是有用的：

　　當(dāng)在查詢中組合相關(guān)表中的數(shù)據(jù)時(shí)，經(jīng)常在聯(lián)接條件中使用外鍵列，方法是將一個(gè)表的外鍵約束中的一列或多列與另一個(gè)表中的主鍵列或唯一鍵列匹配。 索引使 數(shù)據(jù)庫(kù)引擎 可以在外鍵表中快速查找相關(guān)數(shù)據(jù)。 但是，創(chuàng)建此索引并不是必需的。 即使沒(méi)有對(duì)兩個(gè)相關(guān)表定義主鍵或外鍵約束，也可以對(duì)來(lái)自這兩個(gè)表中的數(shù)據(jù)進(jìn)行組合，但兩個(gè)表間的外鍵關(guān)系說(shuō)明已用其鍵作為條件對(duì)其進(jìn)行了優(yōu)化，以便組合到查詢中。

　　對(duì)主鍵約束的更改可由相關(guān)表中的外鍵約束檢查。

　　外鍵約束(foreign key)就是表與表之間的某種約定的關(guān)系，由于這種關(guān)系的存在，我們能夠讓表與表之間的數(shù)據(jù)，更加的完整，關(guān)連性更強(qiáng)。

　　關(guān)于數(shù)據(jù)表的完整性和關(guān)連性，可以舉個(gè)例子

　　有二張表，一張是用戶表，一張是訂單表：

　　1.如果我刪除了用戶表里的用戶，那么訂單表里面跟這個(gè)用戶有關(guān)的數(shù)據(jù)，就成了無(wú)頭數(shù)據(jù)了，不完整了。

　　2.如果我在訂單表里面，隨便插入了一條數(shù)據(jù)，這個(gè)訂單在用戶表里面，沒(méi)有與之對(duì)應(yīng)的用戶。這樣數(shù)據(jù)也不完整了。

　　如果有外鍵的話，就方便多了，可以不讓用戶刪除數(shù)據(jù)，或者刪除用戶的話，通過(guò)外鍵同樣刪除訂單表里面的數(shù)據(jù)，這樣也能讓數(shù)據(jù)完整。

　　通過(guò)外鍵約束，每次插入或更新數(shù)據(jù)表時(shí)，都會(huì)檢查數(shù)據(jù)的完整性。

2.創(chuàng)建外鍵約束

2.1 方法一：通過(guò)create table創(chuàng)建外鍵

　　語(yǔ)法：

  1. create table 數(shù)據(jù)表名稱(
  2. ...,
  3. [CONSTRAINT [約束名稱]] FOREIGN KEY [外鍵字段]
  4. REFERENCES [外鍵表名](外鍵字段，外鍵字段2…..)
  5. [ON DELETE CASCADE ]
  6. [ON UPDATE CASCADE ]
  7. )

　　參數(shù)的解釋：

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

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

注意：on update cascade是級(jí)聯(lián)更新的意思，on delete cascade是級(jí)聯(lián)刪除的意思，意思就是說(shuō)當(dāng)你更新或刪除主鍵表，那外鍵表也會(huì)跟隨一起更新或刪除。

　　精簡(jiǎn)化后的語(yǔ)法：

  1. foreign key 當(dāng)前表的字段 references 外部表名 (關(guān)聯(lián)的字段） type=innodb

2.1.1 插入測(cè)試數(shù)據(jù)

　　例子：我們創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，包含用戶信息表和訂單表

  1. MariaDB [book]> create database market; # 創(chuàng)建market數(shù)據(jù)庫(kù)
  2. Query OK, 1 row affected (0.00 sec)
  3.  
  4. MariaDB [book]> use market; # 使用market數(shù)據(jù)庫(kù)
  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; # 創(chuàng)建userprofile數(shù)據(jù)表，指定使用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); # 創(chuàng)建user_order數(shù)據(jù)表，同時(shí)為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數(shù)據(jù)表插入三條記錄
  14. Query OK, 3 rows affected (0.01 sec)
  15. Records: 3 Duplicates: 0 Warnings: 0
  16.  
  17. MariaDB [market]> select * from userprofile; # 查詢userprofile數(shù)據(jù)表的所有記錄
  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數(shù)據(jù)表插入三條記錄
  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數(shù)據(jù)表的所有記錄
  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; # 聯(lián)表查詢
  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 測(cè)試級(jí)聯(lián)刪除

  1. MariaDB [market]> delete from userprofile where id=1; # 刪除user表中id為1的數(shù)據(jù)
  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表的數(shù)據(jù)
  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 測(cè)試級(jí)聯(lián)更新

　　更新數(shù)據(jù)之前的狀態(tài)

  1. MariaDB [market]> select * from userprofile; # 查看userprofile表的數(shù)據(jù)
  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表的數(shù)據(jù)
  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)

　　更新數(shù)據(jù)?

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

　　更新數(shù)據(jù)后的狀態(tài)?

  1. MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_order where id=u_id; # 聯(lián)表查詢，可以看出表中已經(jīng)沒(méi)有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表的數(shù)據(jù)，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表的數(shù)據(jù),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 測(cè)試數(shù)據(jù)完整性

  1. MariaDB [market]> insert into user_order(u_id,username,money)values(5,"XJ",345); # 單獨(dú)向user_order數(shù)據(jù)表中插入數(shù)據(jù)，插入數(shù)據(jù)失敗
  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里面插入一條數(shù)據(jù)u_id為5用戶，在userprofile表里面根本沒(méi)有，所以插入數(shù)據(jù)失敗

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

  1. MariaDB [market]> insert into userprofile values(5,"XJ",1); # 先向userprofile數(shù)據(jù)表中插入id為5的記錄，插入數(shù)據(jù)成功
  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數(shù)據(jù)表中插入數(shù)據(jù),成功
  5. Query OK, 1 row affected (0.00 sec)
  6.  
  7. MariaDB [market]> select * from userprofile; # 查詢userprofile數(shù)據(jù)表中的所有記錄
  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數(shù)據(jù)表中的所有記錄
  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 方法二：通過(guò)alter table創(chuàng)建外鍵和級(jí)聯(lián)更新，級(jí)聯(lián)刪除

　　語(yǔ)法：

  1. alter table 數(shù)據(jù)表名稱 add
  2. [constraint [約束名稱] ] foreign key (外鍵字段,..) references 數(shù)據(jù)表(參照字段,...)
  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)); # 創(chuàng)建user_order1數(shù)據(jù)表，創(chuàng)建表時(shí)不使用外鍵約束
  2. Query OK, 0 rows affected (0.11 sec)
  3.  
  4. MariaDB [market]> show create table user_order1; # 查看user_order1數(shù)據(jù)表的創(chuàng)建信息，沒(méi)有外鍵約束
  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數(shù)據(jù)表，為user_order1數(shù)據(jù)表添加外鍵約束
  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數(shù)據(jù)表的創(chuàng)建信息，已經(jīng)添加了外鍵約束
  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.刪除外鍵

　　語(yǔ)法

  1. alter table 數(shù)據(jù)表名稱 drop foreign key 約束（外鍵）名稱

　　例子：

  1. MariaDB [market]> show create table user_order1; # 查看user_order1數(shù)據(jù)表的創(chuàng)建信息，包含外鍵約束
  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數(shù)據(jù)表刪除外鍵約束，外鍵名稱必須與從`show create table user_order1`語(yǔ)句中查到的相同
  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數(shù)據(jù)表的創(chuàng)建信息，外鍵約束已經(jīng)被刪除了
  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.使用外鍵約束的條件

　　要想外鍵創(chuàng)建成功，必須滿足以下4個(gè)條件：

　　1、確保參照的表和字段存在。

　　2、組成外鍵的字段被索引。

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

　　4、外鍵字段和關(guān)聯(lián)字段，數(shù)據(jù)類型必須一致。

5.使用外鍵約束需要的注意事項(xiàng)

　　1.on delete cascade on update cascade 添加級(jí)聯(lián)刪除和更新：

　　2.確保參照的表userprofile中id字段存在。

　　3.確保組成外鍵的字段u_id被索引

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

　　5.外鍵字段和關(guān)聯(lián)字段，數(shù)據(jù)類型必須一致。

總結(jié)

　　以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值。