MySQL外鍵詳解，MySQL鍵詳解

MySQL外鍵詳解，MySQL鍵詳解

最近在學習Hibernate的時候，總是被外鍵搞得頭腦發懵，而且，自己的MySQL的外鍵學習也不是很紮實，所以為了更好地掌握Hibernate、MySQL，所以，在網上搜了一些學習MySQL建立外鍵和添加外鍵的部落格，並深刻的學習了一下，感覺爽爆了，媽媽再也不用為我的外鍵擔心了！

下面就是關於外鍵的知識點總結！

    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列不被包括在一個外鍵中，這是因為對這些列的索引必須總是包含一個前置長度。

         ·  如果CONSTRAINT symbol被給出，它在資料庫裡必須是唯一的。如果它沒有被給出，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實現延遲的約束檢查之前，一些事情是不可能的，比如刪除一個通過外鍵參考到自身的記錄。

注釋：當前，觸發器不被級聯外鍵的動作啟用。

一個通過單列外鍵聯絡起父表和子表的簡單例子如下：

CREATE TABLE parent(                    id INT NOT NULL,                    PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(                   id INT, parent_id INT,                   INDEX par_ind (parent_id),                   FOREIGN KEY (parent_id) REFERENCES parent(id)                   ON DELETE CASCADE) TYPE=INNODB;

 

 

如下是一個更複雜的例子，其中一個product_order表對其它兩個表有外鍵。一個外鍵引用一個product表中的雙列索引。另一個引用在customer表中的單行索引：
 

CREATE TABLE product (                      category INT NOT NULL,                      id INT NOT NULL,                      price DECIMAL,                      PRIMARY KEY(category, id)) TYPE=INNODB; CREATE TABLE customer (id INT NOT NULL,                      PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,                      product_category INT NOT NULL,                      product_id INT NOT NULL,                      customer_id INT NOT NULL,                      PRIMARY KEY(no),                      INDEX (product_category, product_id),                      FOREIGN KEY (product_category, product_id)                       REFERENCES product(category, id)                      ON UPDATE CASCADE ON DELETE RESTRICT,                      INDEX (customer_id),                      FOREIGN KEY (customer_id)                       REFERENCES customer(id)) TYPE=INNODB;

InnoDB允許你用ALTER TABLE往一個表中添加一個新的外鍵約束：

ALTER TABLE yourtablename

    ADD [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}]

   記住先建立需要的索引。你也可以用ALTER TABLE往一個表添加一個自引用外鍵約束。

InnoDB也支援使用ALTER TABLE來移除外鍵：

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

      當年建立一個外鍵之時，如果FOREIGN KEY子句包括一個CONSTRAINT名字，你可以引用那個名字來移除外鍵。另外，當外鍵被建立之時，fk_symbol值被InnoDB內部保證。當你想要移除一個外鍵之時，要找出標記，請使用SHOW CREATE TABLE語句。例子如下：

 

mysql> SHOW CREATE TABLE ibtest11c\G ************************ 1. row **************************       Table: ibtest11c Create Table: CREATE TABLE `ibtest11c` (  `A` int(11) NOT NULL auto_increment,  `D` int(11) NOT NULL default '0',  `B` varchar(200) NOT NULL default '',  `C` varchar(175) default NULL,  PRIMARY KEY  (`A`,`D`,`B`),  KEY `B` (`B`,`C`),  KEY `C` (`C`),  CONSTRAINT `0_38775`  FOREIGN KEY (`A`, `D`)  REFERENCES `ibtest11a` (`A`, `D`)  ON DELETE CASCADE ON UPDATE CASCADE,  CONSTRAINT `0_38776`  FOREIGN KEY (`B`, `C`)  REFERENCES `ibtest11a` (`B`, `C`)  ON DELETE CASCADE ON UPDATE CASCADE)  ENGINE=INNODB CHARSET=latin11  row in set (0.01 sec)mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;

 
 

      InnoDB解析程式允許你在FOREIGN KEY ... REFERENCES ...子句中用`（backticks)把表和列名名字圍起來。InnoDB解析程式也考慮到lower_case_table_names系統變數的設定。

     InnoDB返回一個表的外鍵定義作為SHOW CREATE TABLE語句輸出的一部分：

SHOW CREATE TABLE tbl_name;

      從這個版本起，mysqldump也將表的正確定義產生到轉儲檔案中，且並不忘記外鍵。

     你可以如下對一個表顯示外鍵約束：

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

     外鍵約束被列在輸出的Comment列。

當執行外鍵檢查之時，InnoDB對它照看著的子或父記錄設定共用的行級鎖。InnoDB立即檢查外鍵約束，檢查不對事務提交延遲。

要使得對有外鍵關係的表重新載入轉儲檔案變得更容易，mysqldump自動在轉儲輸出中包括一個語句設定FOREIGN_KEY_CHECKS為0。這避免在轉儲被重新裝載之時，與不得不被以特別順序重新裝載的表相關的問題。也可以手動設定這個變數：

mysql> SET FOREIGN_KEY_CHECKS = 0;

mysql> SOURCE dump_file_name;

mysql> SET FOREIGN_KEY_CHECKS = 1;

      如果轉儲檔案包含對外鍵是不正確順序的表，這就以任何順序匯入該表。這樣也加快匯入操作。設定FOREIGN_KEY_CHECKS為0，對於在LOAD DATA和ALTER TABLE操作中忽略外鍵限制也是非常有用的。

InnoDB不允許你刪除一個被FOREIGN KEY資料表條件約束引用的表，除非你做設定SET FOREIGN_KEY_CHECKS=0。當你移除一個表的時候，在它的建立語句裡定義的約束也被移除。

如果你重新建立一個被移除的表，它必須有一個遵從於也引用它的外鍵約束的定義。它必須有正確的列名和類型，並且如前所述，它必須對被引用的鍵有索引。如果這些不被滿足，MySQL返回錯誤號碼1005 並在錯誤資訊字串中指向errno 150。

 

著作權聲明：感覺我寫的還算不錯的的話希望你能夠動動你的滑鼠和鍵盤為我點上一個贊或是為我奉獻上一個評論，在下感激不盡!_______________________________________________________歡迎轉載，希望在你轉載的同時，添加原文地址，謝謝配合