Mysql中增加與取消外鍵約束語句

文法

[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;

設定索引

若要設定外鍵，在參照表(referencing table，即Pc表) 和被參照表 (referenced table，即parts表) 中，相對應的兩個欄位必須都設定索引(index)。

對Parts表：

ALTER TABLE parts ADD INDEX idx_model (model);

這句話的意思是，為 parts 表增加一個索引，索引建立在 model 欄位上，給這個索引起個名字叫idx_model。

對Pc表也類似：

ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel);

事實上這兩個索引可以在建立表的時候就設定。這裡只是為了突出其必要性。

定義外鍵

下面為兩張表之間建立前面所述的那種“約束”。因為pc的CPU型號必須參照parts表中的相應型號，所以我們將Pc表的cpumodel欄位設定為“外鍵”(FOREIGN KEY)，即這個鍵的參照值來自於其他表。

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model
FOREIGN KEY (cpumodel)
REFERENCES parts(model);

第一行是說要為Pc表設定外鍵，給這個外鍵起一個名字叫做fk_cpu_model；第二行是說將本表的cpumodel欄位設定為外鍵；第三行是說這個外鍵受到的約束來自於Parts表的model欄位。

這樣，我們的外鍵就可以了。如果我們試著CREATE一台Pc，它所使用的CPU的型號是Parts表中不存在的，那麼MySQL會禁止這台PC被CREATE出來。

級聯操作

考慮以下這種情況：

技術人員發現，一個月之前輸入到 parts 表中的某個系列的 cpu （可能有很多款）的型號全都輸錯了一個字母，現在需要改正。我們希望的是，當 parts 表中那些 Referenced Column 有所變化時，相應表中的 Referencing Column 也能自動校正。

可以在定義外鍵的時候，在最後加入這樣的關鍵字：

ON UPDATE CASCADE; 即在主表更新時，子表（們）產生連鎖更新動作，似乎有些人喜歡把這個叫“級聯”操作。:)

如果把這語句完整的寫出來，就是：

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model
FOREIGN KEY (cpumodel)
REFERENCES parts(model)
ON UPDATE CASCADE;

除了 CASCADE 外，還有 RESTRICT(禁止主表變更)、SET NULL(子表相應欄位設定為空白)等操作。

Mysql中取消外鍵約束。 .

SET FOREIGN_KEY_CHECKS=0;在Mysql中取消外鍵約束。