外鍵
外鍵的用途是確保數(shù)據(jù)的完整性。它通常包括以下幾種:
1 實體完整性,確保每個實體是唯一的(通過主鍵來實施)
2 域完整性,確保屬性值只從一套特定可選的集合里選擇
3 關(guān)聯(lián)完整性,確保每個外鍵或是NULL(如果允許的話)或含有與相關(guān)主鍵值相配的值
1.什么是外鍵約束
與主鍵約束不同,創(chuàng)建外鍵約束不會自動創(chuàng)建對應(yīng)的索引。 但是由于以下原因,對外鍵手動創(chuàng)建索引通常是有用的:
外鍵約束(foreign key)就是表與表之間的某種約定的關(guān)系,由于這種關(guān)系的存在,我們能夠讓表與表之間的數(shù)據(jù),更加的完整,關(guān)連性更強。
關(guān)于數(shù)據(jù)表的完整性和關(guān)連性,可以舉個例子
有二張表,一張是用戶表,一張是訂單表:
1.如果我刪除了用戶表里的用戶,那么訂單表里面跟這個用戶有關(guān)的數(shù)據(jù),就成了無頭數(shù)據(jù)了,不完整了。
2.如果我在訂單表里面,隨便插入了一條數(shù)據(jù),這個訂單在用戶表里面,沒有與之對應(yīng)的用戶。這樣數(shù)據(jù)也不完整了。
如果有外鍵的話,就方便多了,可以不讓用戶刪除數(shù)據(jù),或者刪除用戶的話,通過外鍵同樣刪除訂單表里面的數(shù)據(jù),這樣也能讓數(shù)據(jù)完整。
通過外鍵約束,每次插入或更新數(shù)據(jù)表時,都會檢查數(shù)據(jù)的完整性。
2.創(chuàng)建外鍵約束
2.1 方法一:通過create table創(chuàng)建外鍵
語法:
create table 數(shù)據(jù)表名稱( ..., [CONSTRAINT [約束名稱]] FOREIGN KEY [外鍵字段] REFERENCES [外鍵表名](外鍵字段,外鍵字段2…..) [ON DELETE CASCADE ] [ON UPDATE CASCADE ] )
參數(shù)的解釋:
RESTRICT: 拒絕對父表的刪除或更新操作。
CASCADE: 從父表刪除或更新且自動刪除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用
注意:on update cascade是級聯(lián)更新的意思,on delete cascade是級聯(lián)刪除的意思,意思就是說當(dāng)你更新或刪除主鍵表,那外鍵表也會跟隨一起更新或刪除。
精簡化后的語法:
foreign key 當(dāng)前表的字段 references 外部表名 (關(guān)聯(lián)的字段) type=innodb
2.1.1 插入測試數(shù)據(jù)
例子:我們創(chuàng)建一個數(shù)據(jù)庫,包含用戶信息表和訂單表
MariaDB [book]> create database market; # 創(chuàng)建market數(shù)據(jù)庫 Query OK, 1 row affected (0.00 sec) MariaDB [book]> use market; # 使用market數(shù)據(jù)庫 Database changed 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引擎 Query OK, 0 rows affected (0.07 sec) 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ù)表,同時為user_order表的u_id字段做外鍵約束,綁定userprofile表的id字段 Query OK, 0 rows affected (0.04 sec) MariaDB [market]> insert into userprofile(name,sex)values('HA',1),('LB',2),('HPC',1); # 向userprofile數(shù)據(jù)表插入三條記錄 Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [market]> select * from userprofile; # 查詢userprofile數(shù)據(jù)表的所有記錄 +----+------+-----+ | id | name | sex | +----+------+-----+ | 1 | HA | 1 | | 2 | LB | 2 | | 3 | HPC | 1 | +----+------+-----+ 3 rows in set (0.00 sec) MariaDB [market]> insert into user_order(u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256); # 向user_order數(shù)據(jù)表插入三條記錄 Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [market]> select * from user_order; # 查詢user_order數(shù)據(jù)表的所有記錄 +------+------+----------+-------+ | o_id | u_id | username | money | +------+------+----------+-------+ | 1 | 1 | HA | 234 | | 2 | 2 | LB | 146 | | 3 | 3 | HPC | 256 | +------+------+----------+-------+ 3 rows in set (0.00 sec) MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_order where id=u_id; # 聯(lián)表查詢 +----+------+-----+-------+------+ | id | name | sex | money | o_id | +----+------+-----+-------+------+ | 1 | HA | 1 | 234 | 1 | | 2 | LB | 2 | 146 | 2 | | 3 | HPC | 1 | 256 | 3 | +----+------+-----+-------+------+ 3 rows in set (0.03 sec)
2.1.2 測試級聯(lián)刪除
MariaDB [market]> delete from userprofile where id=1; # 刪除user表中id為1的數(shù)據(jù) Query OK, 1 row affected (0.01 sec) MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_order where id=u_id; +----+------+-----+-------+------+ | id | name | sex | money | o_id | +----+------+-----+-------+------+ | 2 | LB | 2 | 146 | 2 | | 3 | HPC | 1 | 256 | 3 | +----+------+-----+-------+------+ 2 rows in set (0.00 sec) MariaDB [market]> select * from user_order; # 查看order表的數(shù)據(jù) +------+------+----------+-------+ | o_id | u_id | username | money | +------+------+----------+-------+ | 2 | 2 | LB | 146 | | 3 | 3 | HPC | 256 | +------+------+----------+-------+ 3 rows in set (0.00 sec)
2.1.3 測試級聯(lián)更新
更新數(shù)據(jù)之前的狀態(tài)
MariaDB [market]> select * from userprofile; # 查看userprofile表的數(shù)據(jù) +----+------+-----+ | id | name | sex | +----+------+-----+ | 2 | LB | 2 | | 3 | HPC | 1 | +----+------+-----+ 3 rows in set (0.00 sec) MariaDB [market]> select * from user_order; # 查看order表的數(shù)據(jù) +------+------+----------+-------+ | o_id | u_id | username | money | +------+------+----------+-------+ | 2 | 2 | LB | 146 | | 3 | 3 | HPC | 256 | +------+------+----------+-------+ 3 rows in set (0.00 sec)
更新數(shù)據(jù)
MariaDB [market]> update userprofile set id=6 where id=2; # 把userprofile數(shù)據(jù)表中id為2的用戶改為id為6 Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
更新數(shù)據(jù)后的狀態(tài)
MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_order where id=u_id; # 聯(lián)表查詢,可以看出表中已經(jīng)沒有id為2的用戶了 +----+------+-----+-------+------+ | id | name | sex | money | o_id | +----+------+-----+-------+------+ | 6 | LB | 2 | 146 | 2 | | 3 | HPC | 1 | 256 | 3 | +----+------+-----+-------+------+ 2 rows in set (0.00 sec) MariaDB [market]> select * from userprofile; # 查看userprofile表的數(shù)據(jù),id只剩下3和6 +----+------+-----+ | id | name | sex | +----+------+-----+ | 3 | HPC | 1 | | 6 | LB | 2 | +----+------+-----+ 2 rows in set (0.00 sec) MariaDB [market]> select * from user_order; # 查看user_order表的數(shù)據(jù),u_id也改為6 +------+------+----------+-------+ | o_id | u_id | username | money | +------+------+----------+-------+ | 2 | 6 | LB | 146 | | 3 | 3 | HPC | 256 | +------+------+----------+-------+ 2 rows in set (0.00 sec)
2.1.4 測試數(shù)據(jù)完整性
MariaDB [market]> insert into user_order(u_id,username,money)values(5,"XJ",345); # 單獨向user_order數(shù)據(jù)表中插入數(shù)據(jù),插入數(shù)據(jù)失敗 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表里面根本沒有,所以插入數(shù)據(jù)失敗
先向userprofile表中插入記錄,再向user_order表中插入記錄就可以了
MariaDB [market]> insert into userprofile values(5,"XJ",1); # 先向userprofile數(shù)據(jù)表中插入id為5的記錄,插入數(shù)據(jù)成功 Query OK, 1 row affected (0.01 sec) MariaDB [market]> insert into user_order(u_id,username,money) values(5,"XJ",345); # 再向user_order數(shù)據(jù)表中插入數(shù)據(jù),成功 Query OK, 1 row affected (0.00 sec) MariaDB [market]> select * from userprofile; # 查詢userprofile數(shù)據(jù)表中的所有記錄 +----+------+-----+ | id | name | sex | +----+------+-----+ | 3 | HPC | 1 | | 5 | XJ | 1 | | 6 | LB | 2 | +----+------+-----+ 3 rows in set (0.00 sec) MariaDB [market]> select * from user_order; # 查詢user_order數(shù)據(jù)表中的所有記錄 +------+------+----------+-------+ | o_id | u_id | username | money | +------+------+----------+-------+ | 2 | 6 | LB | 146 | | 3 | 3 | HPC | 256 | | 5 | 5 | XJ | 345 | +------+------+----------+-------+ 3 rows in set (0.01 sec)
2.2 方法二:通過alter table創(chuàng)建外鍵和級聯(lián)更新,級聯(lián)刪除
語法:
alter table 數(shù)據(jù)表名稱 add [constraint [約束名稱] ] foreign key (外鍵字段,..) references 數(shù)據(jù)表(參照字段,...) [on update cascade|set null|no action] [on delete cascade|set null|no action] )
例子:
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)建表時不使用外鍵約束 Query OK, 0 rows affected (0.11 sec) MariaDB [market]> show create table user_order1; # 查看user_order1數(shù)據(jù)表的創(chuàng)建信息,沒有外鍵約束 +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user_order1 | CREATE TABLE `user_order1` ( `o_id` int(11) NOT NULL AUTO_INCREMENT, `u_id` int(11) DEFAULT '0', `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `money` int(11) DEFAULT NULL, PRIMARY KEY (`o_id`), KEY `u_id` (`u_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) 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ù)表添加外鍵約束 Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [market]> show create table user_order1; # 查看user_order1數(shù)據(jù)表的創(chuàng)建信息,已經(jīng)添加了外鍵約束 +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user_order1 | CREATE TABLE `user_order1` ( `o_id` int(11) NOT NULL AUTO_INCREMENT, `u_id` int(11) DEFAULT '0', `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `money` int(11) DEFAULT NULL, PRIMARY KEY (`o_id`), KEY `u_id` (`u_id`), CONSTRAINT `user_order1_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userprofile` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3.刪除外鍵
語法
alter table 數(shù)據(jù)表名稱 drop foreign key 約束(外鍵)名稱
例子:
MariaDB [market]> show create table user_order1; # 查看user_order1數(shù)據(jù)表的創(chuàng)建信息,包含外鍵約束 +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user_order1 | CREATE TABLE `user_order1` ( `o_id` int(11) NOT NULL AUTO_INCREMENT, `u_id` int(11) DEFAULT '0', `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `money` int(11) DEFAULT NULL, PRIMARY KEY (`o_id`), KEY `u_id` (`u_id`), CONSTRAINT `user_order1_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userprofile` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [market]> alter table user_order1 drop foreign key user_order1_ibfk_1; # 為user_order1數(shù)據(jù)表刪除外鍵約束,外鍵名稱必須與從`show create table user_order1`語句中查到的相同 Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [market]> show create table user_order1; # 查看user_order1數(shù)據(jù)表的創(chuàng)建信息,外鍵約束已經(jīng)被刪除了 +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user_order1 | CREATE TABLE `user_order1` ( `o_id` int(11) NOT NULL AUTO_INCREMENT, `u_id` int(11) DEFAULT '0', `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `money` int(11) DEFAULT NULL, PRIMARY KEY (`o_id`), KEY `u_id` (`u_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
4.使用外鍵約束的條件
要想外鍵創(chuàng)建成功,必須滿足以下4個條件:
1、確保參照的表和字段存在。
2、組成外鍵的字段被索引。
3、必須使用type指定存儲引擎為:innodb.
4、外鍵字段和關(guān)聯(lián)字段,數(shù)據(jù)類型必須一致。
5.使用外鍵約束需要的注意事項
1.on delete cascade on update cascade 添加級聯(lián)刪除和更新:
2.確保參照的表userprofile中id字段存在。
3.確保組成外鍵的字段u_id被索引
4.必須使用type指定存儲引擎為:innodb。
5.外鍵字段和關(guān)聯(lián)字段,數(shù)據(jù)類型必須一致。
總結(jié)
以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,如果有疑問大家可以留言交流,謝謝大家對腳本之家的支持。
標(biāo)簽:張掖 酒泉 雞西 阜陽 貴港 萍鄉(xiāng) AXB 衡水
巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MariaDB數(shù)據(jù)庫的外鍵約束實例詳解》,本文關(guān)鍵詞 MariaDB,數(shù)據(jù)庫,的,外鍵,約束,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。