MariaDB數據庫的外鍵約束實例詳解_mariadb

來源:腳本之家  責任編輯:小易  

外鍵是該表是另一個表之間聯接的字段外鍵必須為另一個表中的主鍵外鍵的用途是確保數據的完整性。它通常包括以下幾種:實體完整性,確保每個實體是唯一的(通過主鍵來實施)域完整性,確保屬性值只從一套特定可選的集合里選擇關聯完整性,確保每個外鍵或是NULL(如果允許的話)或含有與相關主鍵值相配的值例子:表A 字典表,表B 業務表。外鍵應建立在表B 上。1、表B 的字典項目的代碼只能是表A 中代碼內容。2、表B 可以無條件刪除記錄。3、表A 在刪除的時候,將根據外鍵的規則,判斷表B 中是否使用了要刪除數據的代碼,如果有引用,則不能刪除。ORACLE 如何建立表外鍵例:學生表student(id,name,sex)成績表score(id,math)如何創建表,要求 有主鍵,有約束 解:CREATE TABLE STUDENT(ID CHAR(10),NAME VARCHAR(8),SEX CHAR(1));ALTER TABLE STUDENT ADD CONSTRAINT PK_STUDENT PRIMARY KEY(ID);CREATE TABLE SCORE(ID CHAR(10),MATH NUMBER(5,2));ALTER TABLE SCORE ADD CONSTRAINT FK_SCROE FOREIGN KEY(ID)REFERENCES STUDENT(ID);主鍵與外鍵:鍵是表中的列(可以是一列,也可以是幾列),主鍵用于唯一的標識表中的數據項;外鍵用于連e69da5e6ba90e799bee5baa631333337623535接父表和子表。而所謂的父表和子表是根據3NF 范式的要求,為了消除傳遞依賴,將原表拆成2個相互關聯的表,而這個關聯就是外鍵www.anxorj.tw防采集請勿采集本網。

外鍵

外鍵的用途是確保數據的完整性。它通常包括以下幾種:

2、外鍵(foreign key):一張表(答關系)內的列(屬性)它同時存在表1和表2中,它不是表1的主鍵,而是表2的主鍵,就可以說他是表1的外鍵。3、候選鍵容(Candidate Key):能唯一標識表(關系)中行(元組)

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

你在建立外鍵的時候要指定更新的關聯,這樣刪除User中的一筆記錄,所有以User表的UserId為外鍵的表的記錄都會刪除,修改User中UserId的值,所有所有以User表的UserId為外鍵的表的記錄都會更新這個

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

alter table 表 add constraint 外鍵約束名稱 foreign key(列)references 表(主鍵列);

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

外鍵的用途是確保數據的完整性。它通常包括以下幾種: 實體完整性,確保每個實體是唯一的(通過主鍵來實施) 域完整性,確保屬性值只從一套特定可選的集合里選擇 關聯完整性,確保每個外鍵或是NULL(如果

1.什么是外鍵約束

先在S表里添加S2的記錄 在P表里,添加P4的記錄 在J表里添加J6的記錄 最后才能在SPJ里添加你那條記錄 前三個順序可以隨意調換,但是最后一條必須在滿足上述三個的情況下才可以添加 不知道你的添加

與主鍵約束不同,創建外鍵約束不會自動創建對應的索引。 但是由于以下原因,對外鍵手動創建索引通常是有用的: 當在查詢中組合相關表中的數據時,經常在聯接條件中使用外鍵列,方法是將一個表的外鍵約束中的一列或多列與另一個表中的主鍵列或唯一鍵列匹配。 索引使 數據庫引擎 可以在外鍵表中快速查找相關數據。 但是,創建此索引并不是必需的。 即使沒有對兩個相關表定義主鍵或外鍵約束,也可以對來自這兩個表中的數據進行組合,但兩個表間的外鍵關系說明已用其鍵作為條件對其進行了優化,以便組合到查詢中。 對主鍵約束的更改可由相關表中的外鍵約束檢查。

外鍵約束(foreign key)就是表與表之間的某種約定的關系,由于這種關系的存在,我們能夠讓表與表之間的數據,更加的完整,關連性更強。

關于數據表的完整性和關連性,可以舉個例子

有二張表,一張是用戶表,一張是訂單表:

1.如果我刪除了用戶表里的用戶,那么訂單表里面跟這個用戶有關的數據,就成了無頭數據了,不完整了。

2.如果我在訂單表里面,隨便插入了一條數據,這個訂單在用戶表里面,沒有與之對應的用戶。這樣數據也不完整了。

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

通過外鍵約束,每次插入或更新數據表時,都會檢查數據的完整性。

2.創建外鍵約束

2.1 方法一:通過create table創建外鍵

語法:

create table 數據表名稱(...,[CONSTRAINT [約束名稱]] FOREIGN KEY [外鍵字段] REFERENCES [外鍵表名](外鍵字段,外鍵字段2…..) [ON DELETE CASCADE ] [ON UPDATE CASCADE ])

參數的解釋:

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

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

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

精簡化后的語法:

foreign key 當前表的字段 references 外部表名 (關聯的字段) type=innodb

2.1.1 插入測試數據

例子:我們創建一個數據庫,包含用戶信息表和訂單表

MariaDB [book]> create database market; # 創建market數據庫Query OK, 1 row affected (0.00 sec)MariaDB [book]> use market; # 使用market數據庫Database changedMariaDB [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引擎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); # 創建user_order數據表,同時為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數據表插入三條記錄Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0MariaDB [market]> select * from userprofile; # 查詢userprofile數據表的所有記錄+----+------+-----+| 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數據表插入三條記錄Query OK, 3 rows affected (0.02 sec)Records: 3 Duplicates: 0 Warnings: 0MariaDB [market]> select * from user_order; # 查詢user_order數據表的所有記錄+------+------+----------+-------+| 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; # 聯表查詢+----+------+-----+-------+------+| 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 測試級聯刪除

MariaDB [market]> delete from userprofile where id=1; # 刪除user表中id為1的數據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表的數據+------+------+----------+-------+| o_id | u_id | username | money |+------+------+----------+-------+| 2 | 2 | LB | 146 || 3 | 3 | HPC | 256 |+------+------+----------+-------+3 rows in set (0.00 sec)

2.1.3 測試級聯更新

更新數據之前的狀態

MariaDB [market]> select * from userprofile; # 查看userprofile表的數據+----+------+-----+| id | name | sex |+----+------+-----+| 2 | LB | 2 || 3 | HPC | 1 |+----+------+-----+3 rows in set (0.00 sec)MariaDB [market]> select * from user_order; # 查看order表的數據+------+------+----------+-------+| o_id | u_id | username | money |+------+------+----------+-------+| 2 | 2 | LB | 146 || 3 | 3 | HPC | 256 |+------+------+----------+-------+3 rows in set (0.00 sec)

更新數據

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

更新數據后的狀態

MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_order where id=u_id; # 聯表查詢,可以看出表中已經沒有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表的數據,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表的數據,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 測試數據完整性

MariaDB [market]> insert into user_order(u_id,username,money)values(5,"XJ",345); # 單獨向user_order數據表中插入數據,插入數據失敗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表中插入記錄就可以了

MariaDB [market]> insert into userprofile values(5,"XJ",1); # 先向userprofile數據表中插入id為5的記錄,插入數據成功Query OK, 1 row affected (0.01 sec)MariaDB [market]> insert into user_order(u_id,username,money) values(5,"XJ",345); # 再向user_order數據表中插入數據,成功Query OK, 1 row affected (0.00 sec)MariaDB [market]> select * from userprofile; # 查詢userprofile數據表中的所有記錄+----+------+-----+| 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數據表中的所有記錄+------+------+----------+-------+| 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創建外鍵和級聯更新,級聯刪除

語法:

alter table 數據表名稱 add [constraint [約束名稱] ] foreign key (外鍵字段,..) references 數據表(參照字段,...) [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)); # 創建user_order1數據表,創建表時不使用外鍵約束Query OK, 0 rows affected (0.11 sec)MariaDB [market]> show create table user_order1; # 查看user_order1數據表的創建信息,沒有外鍵約束+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 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數據表,為user_order1數據表添加外鍵約束Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0MariaDB [market]> show create table user_order1; # 查看user_order1數據表的創建信息,已經添加了外鍵約束+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 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 數據表名稱 drop foreign key 約束(外鍵)名稱

例子:

MariaDB [market]> show create table user_order1; # 查看user_order1數據表的創建信息,包含外鍵約束+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 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數據表刪除外鍵約束,外鍵名稱必須與從`show create table user_order1`語句中查到的相同Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0MariaDB [market]> show create table user_order1; # 查看user_order1數據表的創建信息,外鍵約束已經被刪除了+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 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.使用外鍵約束的條件

要想外鍵創建成功,必須滿足以下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.外鍵字段和關聯字段,數據類型必須一致。

總結

以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作具有一定的參考學習價值,如果有疑問大家可以留言交流,謝謝大家對真格學網的支持。

說明send和accept都是user表的外鍵。constraint f_key foreign key(send)references user(username);constraint f_key2 foreign key(accept)references user(username);對于messagebox主鍵表就是本身嘛,外鍵表自然就是user表了即user表是messagebox表的父表內容來自www.anxorj.tw請勿采集。


  • 本文相關:
  • 淺談mysql和mariadb區別
  • centos 7安裝mysql5.5和安裝 mariadb使用的命令
  • centos7 下mysql重新啟動mariadb篇
  • mac中mariadb數據庫的安裝步驟
  • mariadb的主從復制、主主復制、半同步復制配置詳解
  • centos安裝和設置mariadb的教程
  • 關于mariadb安裝問題小記(cmake error at)
  • 記一次mariadb數據庫無法連接
  • centos 7中成功安裝mariadb的方法教程
  • mariadb性能調優工具mytop的使用詳解
  • mariadb數據庫的外鍵約束實例詳解
  • centos 7中成功安裝mariadb的方法教程
  • 關于mongoose連接mongodb重復訪問報錯的解決辦法
  • centos 7安裝mysql5.5和安裝 mariadb使用的命令
  • centos安裝和設置mariadb的教程
  • centos中找回mariadb數據庫root用戶權限的方法
  • window7安裝mariadb數據庫及系統初始化操作分析
  • exchange在接收連接器上啟用匿名中繼的方法
  • windows10系統下安裝mariadb 的教程圖解
  • mariadb的主從復制、主主復制、半同步復制配置詳解
  • 數據庫外鍵約束。
  • 數據庫中的外鍵約束
  • 數據庫表中有外鍵,該怎么添加數據?
  • 數據庫建表時一個表最多可以有幾個主鍵,幾個外鍵
  • 數據庫里面的主鍵和外鍵及候選鍵是什么意思?
  • 如何修改數據庫中主外鍵約束的數據
  • 表已創建好了,想添加外鍵約束,代碼怎么寫
  • 想問一下數據庫設置外鍵約束的主要目的
  • SQL中怎么向有外鍵約束的表中添加數據
  • oracle數據庫里,主鍵約束里的主鍵怎么理解?外鍵呢?舉例說明一下
  • 網站首頁網頁制作腳本下載服務器操作系統網站運營平面設計媒體動畫電腦基礎硬件教程網絡安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess數據庫文摘數據庫其它首頁mariadb淺談mysql和mariadb區別centos 7安裝mysql5.5和安裝 mariadb使用的命令centos7 下mysql重新啟動mariadb篇mac中mariadb數據庫的安裝步驟mariadb的主從復制、主主復制、半同步復制配置詳解centos安裝和設置mariadb的教程關于mariadb安裝問題小記(cmake error at)記一次mariadb數據庫無法連接centos 7中成功安裝mariadb的方法教程mariadb性能調優工具mytop的使用詳解mariadb數據庫的外鍵約束實例詳解centos 7中成功安裝mariadb的方法教程關于mongoose連接mongodb重復訪問報錯的解決辦法centos 7安裝mysql5.5和安裝 mariadb使用的命令centos安裝和設置mariadb的教程centos中找回mariadb數據庫root用戶權限的方法window7安裝mariadb數據庫及系統初始化操作分析exchange在接收連接器上啟用匿名中繼的方法windows10系統下安裝mariadb 的教程圖解mariadb的主從復制、主主復制、半同步復制配置詳解淺談mysql和mariadb區別centos 7安裝mysql5.5和安裝 marcentos7 下mysql重新啟動mariadbmariadb中1045權限錯誤導致拒絕用mac中mariadb數據庫的安裝步驟mysql分支選擇參考:percona還是詳解centos 使用yum安裝mariadbmariadb的主從復制、主主復制、半記一次mariadb數據庫無法連接centos安裝和設置mariadb的教程windows10系統下安裝mariadb 的教程圖解centos7 下mysql重新啟動mariadb篇mariadb性能調優工具mytop的使用詳解關于mongoose連接mongodb重復訪問報錯的解mariadb中的thread pool詳細介紹和使用方mac中mariadb數據庫的安裝步驟windows server 2016 服務器配置指南 之 mysql分支選擇參考:percona還是mariadbcentos 7中成功安裝mariadb的方法教程mariadb的主從復制、主主復制、半同步復制
    免責聲明 - 關于我們 - 聯系我們 - 廣告聯系 - 友情鏈接 - 幫助中心 - 頻道導航
    Copyright © 2017 www.anxorj.tw All Rights Reserved
    陕西快乐10分下载