MySQL之外鍵約束

轉：http://www.cnblogs.com/yidianfeng/archive/2011/02/24/1964148.html

MySQL有兩種常用的引擎類型：MyISAM和InnoDB。目前只有InnoDB引擎類型支援外鍵約束。InnoDB中外鍵約束定義的文法如下：

[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  外鍵的使用需要滿足下列的條件：

  1. 兩張表必須都是InnoDB表，並且它們沒有暫存資料表。

  2. 建立外鍵關係的對應列必須具有相似的InnoDB內部資料類型。

  3. 建立外鍵關係的對應列必須建立了索引。

  4. 假如顯式的給出了CONSTRAINT symbol，那symbol在資料庫中必須是唯一的。假如沒有顯式的給出，InnoDB會自動的建立。

  如果子表試圖建立一個在父表中不存在的外索引值，InnoDB會拒絕任何INSERT或UPDATE操作。如果父表試圖UPDATE或者DELETE任何子表中存在或匹配的外索引值，最終動作取決於外鍵約束定義中的ON UPDATE和ON DELETE選項。InnoDB支援5種不同的動作，如果沒有指定ON DELETE或者ON UPDATE，預設的動作為RESTRICT:

  1. CASCADE: 從父表中刪除或更新對應的行，同時自動的刪除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支援。

  2. SET NULL: 從父表中刪除或更新對應的行，同時將子表中的外鍵列設為空白。注意，這些在外鍵列沒有被設為NOT NULL時才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支援。

  3. NO ACTION: InnoDB拒絕刪除或者更新父表。

  4. RESTRICT: 拒絕刪除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE選項的效果是一樣的。

  5. SET DEFAULT: InnoDB目前不支援。

  外鍵約束使用最多的兩種情況無外乎：

  1）父表更新時子表也更新，父表刪除時如果子表有匹配的項，刪除失敗；

  2）父表更新時子表也更新，父表刪除時子表匹配的項也刪除。

  前一種情況，在外鍵定義中，我們使用ON UPDATE CASCADE ON DELETE RESTRICT；後一種情況，可以使用ON UPDATE CASCADE ON DELETE CASCADE。

  InnoDB允許你使用ALTER TABLE在一個已經存在的表上增加一個新的外鍵:

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來刪除外鍵：
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

 

 

InnoDB也支援外鍵約束。InnoDB中對外鍵約束定義的文法看起來如下：

 

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)

 

    REFERENCES tbl_name (index_col_name, ...)

 

    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

 

    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

 

外鍵定義服從下列情況：

 

· 所有tables必須是InnoDB型，它們不能是暫存資料表。

· 在參考資料表中，必須有一個索引，外鍵列以同樣的順序被列在其中作為第一列。這樣一個索引如果不存在，它必須在參考資料表裡被自動建立。

· 在參考資料表中，必須有一個索引，被引用的列以同樣的順序被列在其中作為第一列。

· 不支援對外鍵列的索引首碼。這樣的後果之一是BLOB和TEXT列不被包括在一個外鍵中，這是因為對這些列的索引必須總是包含一個前置長度。

· 如果CONSTRAINTsymbol被給出，它在資料庫裡必須是唯一的。如果它沒有被給出，InnoDB自動建立這個名字。

 

InnoDB拒絕任何試著在子表建立一個外索引值而不匹配在父表中的候選索引鍵值的INSERT或UPDATE操作。一個父表有一些匹配的行的子表，InnoDB對任何試圖更新或刪除該父表中候選索引鍵值的UPDATE或DELETE操作有所動作，這個動作取決於用FOREIGN KEY子句的ON UPDATE和ON DETETE子句指定的referential action。當使用者試圖從一個父表刪除或更新一行之時，且在子表中有一個或多個匹配的行，InnoDB根據要採取的動作有五個選擇：

 

· CASCADE: 從父表刪除或更新且自動刪除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。在兩個表之間，你不應定義若干在父表或子表中的同一列採取動作的ON UPDATE CASCADE子句。

· SET NULL: 從父表刪除或更新行，並設定子表中的外鍵列為NULL。如果外鍵列沒有指定NOT NULL限定詞，這就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支援。

· NO ACTION: 在ANSI SQL-92標準中，NO ACTION意味這不採取動作，就是如果有一個相關的外索引值在被參考的表裡，刪除或更新主要索引值的企圖不被允許進行（Gruber, 掌握SQL, 2000:181）。 InnoDB拒絕對父表的刪除或更新操作。

· RESTRICT: 拒絕對父表的刪除或更新操作。NO ACTION和RESTRICT都一樣，刪除ON DELETE或ON UPDATE子句。（一些資料庫系統有延期檢查，並且NO ACTION是一個延期檢查。在MySQL中，外鍵約束是被立即檢查的，所以NO ACTION和RESTRICT是同樣的）。

· SET DEFAULT: 這個動作被解析程式識別，但InnoDB拒絕包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子句的表定義。

 

當父表中的候選索引鍵被更新的時候，InnoDB支援同樣選擇。選擇CASCADE，在子表中的外鍵列被設定為父表中候選索引鍵的新值。以同樣的方式，如果在子表更新的列參考在另一個表中的外鍵，更新級聯。

 

注意，InnoDB支援外鍵在一個表內引用，在這些情況下，子表實際上意味這在表內附屬的記錄。

 

InnoDB需要對外鍵和參考索引鍵的索引以便外鍵檢查可以快速進行且不需要一個表掃描。對外鍵的索引被自動建立。這是相對於一些老版本，在老版本中索引必須明確建立，否則外鍵約束的建立會失敗。

 

在InnoDB內，外鍵裡和被引用列裡相應的列必須有類似的內部資料類型，以便它們不需類型轉換就可被比較。整數類型的大小和符號必須相同。字串類型的長度不需要相同。如果你指定一個SET NULL動作，請確認你沒有在子表中宣告該列為為NOT NULL。

 

如果MySQL從CREATE TABLE語句報告一個錯誤號碼1005，並且錯誤資訊字串指向errno 150，這意思是因為一個外鍵約束被不正確形成，表建立失敗。類似地，如果ALTER TABLE失敗，且它指向errno 150， 那意味著對已變更的表，外鍵定義會被不正確的形成。你可以使用SHOW INNODB STATUS來顯示一個對伺服器上最近的InnoDB外鍵錯誤的詳細解釋。

 

注釋：InnoDB不對那些外鍵或包含NULL列的參考索引鍵值檢查外鍵約束。

 

對SQL標準的背離：如果在父表內有數個行，其中有相同的參考索引鍵值，然後InnoDB在外鍵檢查中採取動作，就彷彿其它有相同索引值的父行不存在一樣。例如，如果你已定義一個RESTRICT類型的約束，並且有一個帶數個父行的子行，InnoDB不允許任何對這些父行的刪除。

 

居於對應外鍵約束的索引內的記錄，InnoDB通過深度優先選法施行級聯操作。

 

對SQL標準的背離： 如果ON UPDATE CASCADE或ON UPDATE SET NULL遞迴更新相同的表，之前在級聯過程中該表一被更新過，它就象RESTRICT一樣動作。這意味著你不能使用自引用ON UPDATE CASCADE或者ON UPDATE SET NULL操作。這將阻止串聯更新導致的無限迴圈。另一方面，一個自引用的ON DELETE SET NULL是有可能的，就像一個自引用ON DELETE CASCADE一樣。級聯操作不可以被嵌套超過15層深。

 

對SQL標準的背離： 類似一般的MySQL，在一個插入，刪除或更新許多行的SQL語句內，InnoDB逐行檢查UNIQUE和FOREIGN KEY約束。按照SQL的標準，預設的行為應被延遲檢查，即約束僅在整個SQL語句被處理之後才被檢查。直到InnoDB實現延遲的約束檢查之前，一些事情是不可能的，比如刪除一個通過外鍵參考到自身的記錄。