mysql cascade|restrict|no action|set null__mysql 外键的几种约束
mysql cascade|restrict|no action|set null
MySQL有兩種常用的引擎類(lèi)型：MyISAM和InnoDB。目前只有InnoDB引擎類(lèi)型支持外鍵約束。InnoDB中外鍵約束定義的語(yǔ)法如下：
[CONSTRAINT [symbol]] FOREIGN KEY[index_name] (index_col_name, ...)REFERENCES tbl_name (index_col_name,...)[ON DELETE reference_option][ON UPDATE reference_option]reference_option:RESTRICT | CASCADE | SET NULL | NO ACTION外鍵的使用需要滿足下列的條件：
如果子表試圖創(chuàng)建一個(gè)在父表中不存在的外鍵值，InnoDB會(huì)拒絕任何INSERT或UPDATE操作。如果父表試圖UPDATE或者DELETE任何子表中存在或匹配的外鍵值，最終動(dòng)作取決于外鍵約束定義中的ON UPDATE和ON DELETE選項(xiàng)。InnoDB支持5種不同的動(dòng)作，如果沒(méi)有指定ON DELETE或者ON UPDATE，默認(rèn)的動(dòng)作為RESTRICT:
外鍵約束使用最多的兩種情況無(wú)外乎：
1）父表更新時(shí)子表也更新，父表刪除時(shí)如果子表有匹配的項(xiàng)，刪除失敗；
2）父表更新時(shí)子表也更新，父表刪除時(shí)子表匹配的項(xiàng)也刪除。
前一種情況，在外鍵定義中，我們使用ON UPDATE CASCADE ON DELETE RESTRICT；后一種情況，可以使用ON UPDATE CASCADE ON DELETE CASCADE。
InnoDB允許你使用ALTER TABLE在一個(gè)已經(jīng)存在的表上增加一個(gè)新的外鍵:
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, …) REFERENCES tbl_name (index_col_name,…) [ON DELETE reference_option] [ON UPDATE reference_option]InnoDB也支持使用ALTER TABLE來(lái)刪除外鍵：
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;MySql 外鍵約束 之CASCADE、SET NULL、RESTRICT、NO ACTION分析和作用
今天遇到數(shù)據(jù)庫(kù)的一個(gè)問(wèn)題，就是每當(dāng)從主表中刪除一個(gè)主鍵id時(shí)，由于與他相關(guān)聯(lián)的表中有相關(guān)的字段數(shù)據(jù)，所以無(wú)法刪除。查了一大堆資料，發(fā)現(xiàn)我建表關(guān)聯(lián)用的是on Restrict.可能問(wèn)題就出在這里吧。
外鍵約束對(duì)子表的含義:
如果在父表中找不到候選鍵,則不允許在子表上進(jìn)行insert/update
外鍵約束對(duì)父表的含義:
在父表上進(jìn)行update/delete以更新或刪除在子表中有一條或多條對(duì)應(yīng)匹配行的候選鍵時(shí),父表的行為取決于：在定義子表的外鍵時(shí)指定的on update/on delete子句, InnoDB支持５種方式, 分列如下
cascade方式
在父表上update/delete記錄時(shí)，同步update/delete掉子表的匹配記錄
On delete cascade從mysql3.23.50開(kāi)始可用; on update cascade從mysql4.0.8開(kāi)始可用
set null方式
在父表上update/delete記錄時(shí)，將子表上匹配記錄的列設(shè)為null
要注意子表的外鍵列不能為not null
On delete set null從mysql3.23.50開(kāi)始可用; on update set null從mysql4.0.8開(kāi)始可用
No action方式
如果子表中有匹配的記錄,則不允許對(duì)父表對(duì)應(yīng)候選鍵進(jìn)行update/delete操作
這個(gè)是ANSI SQL-92標(biāo)準(zhǔn),從mysql4.0.8開(kāi)始支持
Restrict方式
同no action, 都是立即檢查外鍵約束
Set default方式
解析器認(rèn)識(shí)這個(gè)action,但I(xiàn)nnodb不能識(shí)別，不知道是什么意思．．．
注意：trigger不會(huì)受外鍵cascade行為的影響,即不會(huì)解發(fā)trigger
在mysql中，與ＳＱＬ標(biāo)準(zhǔn)相違背的三點(diǎn)
如果在父表中有多個(gè)key值相同，那么在進(jìn)行外鍵check時(shí),會(huì)當(dāng)成有相同key值的其他行不存在; 比如當(dāng)定義了一個(gè)restrict行為外鍵時(shí),一個(gè)子表行對(duì)應(yīng)到多個(gè)父表行(具有相同key值), Innodb不允許刪除父表上的所有這些行
父子表是同一個(gè)表,自我參照時(shí)不允許指定on update cascade, on update set null
從mysql4.0.13開(kāi)始，允許同一個(gè)表上的on delete set null
從mysql4.0.21開(kāi)始，允許同一個(gè)表上的on delete cascade
但級(jí)聯(lián)層次不能超出15
Innodb在檢查unique,constraint約束時(shí)，是row by row而不是語(yǔ)句或事務(wù)結(jié)束;
SQL標(biāo)準(zhǔn)中對(duì)constraint的檢查是在語(yǔ)句執(zhí)行完成時(shí)
NULL、RESTRICT、NO ACTION
刪除：從表記錄不存在時(shí)，主表才可以刪除。刪除從表，主表不變
更新：從表記錄不存在時(shí)，主表才可以更新。更新從表，主表不變
CASCADE
刪除：刪除主表時(shí)自動(dòng)刪除從表。刪除從表，主表不變
更新：更新主表時(shí)自動(dòng)更新從表。更新從表，主表不變
SET NULL
刪除：刪除主表時(shí)自動(dòng)更新從表值為NULL。刪除從表，主表不變
更新：更新主表時(shí)自動(dòng)更新從表值為NULL。更新從表，主表不變
外鍵約束屬性： RESTRICT | CASCADE | SET NULL | NO ACTION 外鍵的使用需要滿足下列的條件：
兩張表必須都是InnoDB表，并且它們沒(méi)有臨時(shí)表。
建立外鍵關(guān)系的對(duì)應(yīng)列必須具有相似的InnoDB內(nèi)部數(shù)據(jù)類(lèi)型。
建立外鍵關(guān)系的對(duì)應(yīng)列必須建立了索引。
假如顯式的給出了CONSTRAINT symbol，那symbol在數(shù)據(jù)庫(kù)中必須是唯一的。假如沒(méi)有顯式的給出，InnoDB會(huì)自動(dòng)的創(chuàng)建。
如果子表試圖創(chuàng)建一個(gè)在父表中不存在的外鍵值，InnoDB會(huì)拒絕任何INSERT或UPDATE操作。如果父表試圖UPDATE或者DELETE任何子表中存在或匹配的外鍵值，最終動(dòng)作取決于外鍵約束定義中的ON UPDATE和ON DELETE選項(xiàng)。InnoDB支持5種不同的動(dòng)作，如果沒(méi)有指定ON DELETE或者ON UPDATE，默認(rèn)的動(dòng)作為RESTRICT:
CASCADE: 從父表中刪除或更新對(duì)應(yīng)的行，同時(shí)自動(dòng)的刪除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。
SET NULL: 從父表中刪除或更新對(duì)應(yīng)的行，同時(shí)將子表中的外鍵列設(shè)為空。注意，這些在外鍵列沒(méi)有被設(shè)為NOT NULL時(shí)才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
NO ACTION: InnoDB拒絕刪除或者更新父表。
RESTRICT: 拒絕刪除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略O(shè)N DELETE或者ON UPDATE選項(xiàng)的效果是一樣的。
SET DEFAULT: InnoDB目前不支持。
外鍵約束使用最多的兩種情況無(wú)外乎：
1）父表更新時(shí)子表也更新，父表刪除時(shí)如果子表有匹配的項(xiàng)，刪除失敗；
2）父表更新時(shí)子表也更新，父表刪除時(shí)子表匹配的項(xiàng)也刪除。
前一種情況，在外鍵定義中，我們使用ON UPDATE CASCADE ON DELETE RESTRICT；后一種情況，可以使用ON UPDATE CASCADE ON DELETE CASCADE。
當(dāng)執(zhí)行外鍵檢查之時(shí)，InnoDB對(duì)它照看著的子或父記錄設(shè)置共享的行級(jí)鎖。InnoDB立即檢查外鍵約束，檢查不對(duì)事務(wù)提交延遲。
要使得對(duì)有外鍵關(guān)系的表重新載入轉(zhuǎn)儲(chǔ)文件變得更容易，mysqldump自動(dòng)在轉(zhuǎn)儲(chǔ)輸出中包括一個(gè)語(yǔ)句設(shè)置FOREIGN_KEY_CHECKS為0。這避免在轉(zhuǎn)儲(chǔ)被重新裝載之時(shí)，與不得不被以特別順序重新裝載的表相關(guān)的問(wèn)題。也可以手動(dòng)設(shè)置這個(gè)變量：
mysql> SET FOREIGN_KEY_CHECKS = 0;mysql> SOURCE dump_file_name;mysql> SET FOREIGN_KEY_CHECKS = 1;如果轉(zhuǎn)儲(chǔ)文件包含對(duì)外鍵是不正確順序的表，這就以任何順序?qū)朐摫怼＿@樣也加快導(dǎo)入操作。設(shè)置FOREIGN_KEY_CHECKS為0，對(duì)于在LOAD DATA和ALTER TABLE操作中忽略外鍵限制也是非常有用的。
InnoDB不允許你刪除一個(gè)被FOREIGN KEY表約束引用的表，除非你做設(shè)置SET FOREIGN_KEY_CHECKS=0。當(dāng)你移除一個(gè)表的時(shí)候，在它的創(chuàng)建語(yǔ)句里定義的約束也被移除。
如果你重新創(chuàng)建一個(gè)被移除的表，它必須有一個(gè)遵從于也引用它的外鍵約束的定義。它必須有正確的列名和類(lèi)型，并且如前所述，它必須對(duì)被引用的鍵有索引。如果這些不被滿足，MySQL返回錯(cuò)誤號(hào)1005 并在錯(cuò)誤信息字符串中指向errno 150。
restrict方式 同no action, 都是立即檢查外鍵約束
--限制，指的是如果字表引用父表的某個(gè)字段的值，那么不允許直接刪除父表的該值；
cascade方式 在父表上update/delete記錄時(shí)，同步update/delete掉子表的匹配記錄 On delete cascade從mysql3.23.50開(kāi)始可用; on update cascade從mysql4.0.8開(kāi)始可用
--級(jí)聯(lián)，刪除父表的某條記錄，子表中引用該值的記錄會(huì)自動(dòng)被刪除；
No action方式 如果子表中有匹配的記錄,則不允許對(duì)父表對(duì)應(yīng)候選鍵進(jìn)行update/delete操作 這個(gè)是ANSI SQL-92標(biāo)準(zhǔn),從mysql4.0.8開(kāi)始支持
--無(wú)參照完整性關(guān)系，有了也不生效。
set null方式 在父表上update/delete記錄時(shí)，將子表上匹配記錄的列設(shè)為null 要注意子表的外鍵列不能為not null On delete set null從mysql3.23.50開(kāi)始可用; on update set null從mysql4.0.8開(kāi)始可用
總結(jié)
以上是生活随笔為你收集整理的mysql cascade|restrict|no action|set null__mysql 外键的几种约束的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： MySQL操作之JSON数据类型操作详解
- 下一篇： linux内核杂记(4)-线程（1）