4.3 外鍵約束

主鍵用于標識表中的數據，而外鍵用于記錄表之間的聯系。一個數據庫中的表通常是相互關聯的，如學生選課系統中，學生要根據課程表來填寫選課表，而選課表根據學生表中的學生編號和課程表中的課程編號來確定每個同學所選的課程。選課表中的學生編號和課程編號即為選課表的外鍵，引用學生表和課程表中的數據。本節介紹外鍵約束的概念和使用。

4.3.1 外鍵約束概述

外鍵記錄了表與表之間字段的聯系。以學生選課系統來說，學生選課表中，需要記錄選課學生的信息，所選的科目，該科目的學分，該學生考試成績等。

學生信息所涉及的內容有很多，包括學生姓名、所在班級等信息，而同一個班的學生也有著重名的情況，若將學生的信息放在課程表中，課程表將變得復雜、難以理解，而且并不能夠確定每個學生所選的科目究竟是怎樣的。

因此需要根據學生表中的主鍵sid字段，來確定唯一的學生，并放在選課表中作為一個字段。此時，選課表根據該字段的值，可在學生信息表中進行查詢，以確定該條記錄所屬的學生信息，而不需要將學生的詳細信息放在選課表中。

同樣的道理，選課表中需要有課程信息，包括該課程的名稱，所屬院系，講課教師信息等，但在選課表中放這些信息，只能使選課表變得復雜難以理解。因此使用課程表中的課程主鍵cid來確定課程信息。

總體來說，外鍵有著以下兩個作用。

（1）讓數據庫自己通過外鍵來保證數據的完整性和一致性。

（2）能夠增加數據庫表關系的可讀性。

上述第一條作用，通過外鍵來確保數據的完整性和一致性。以上述選課表為例，該表引用學生表的主鍵sid字段，在該表中需要有一個字段（假設命名為csid），存儲學生sid信息，以便根據學生sid信息查詢學生信息。那么，csid字段中的值必須在學生表的sid字段中有記錄，而且學生表在刪除學生信息時，需要確保選課表中沒有該學生的記錄。

同樣，選課表中關于課程表的信息，在課程表中必須有記錄，而且課程表若需要刪除時，需要確保選課表中沒有該課程，否則將出錯。

外鍵的使用將不同表的字段關聯起來，這些數據在修改、刪除時有著關聯。外鍵除了關聯著表之間的聯系，還將在數據操作時維護數據完整性。

外鍵的定義需要服從下列幾種情況。

（1）所有tables必須是InnoDB型，它們不能是臨時表。因為在MySQL中只有InnoDB類型的表才支持外鍵。

（2）所有要建立外鍵的字段必須建立約束。

（3）對于非InnoDB表，FOREIGN KEY子句會被忽略掉。

對外鍵的操作包括，添加外鍵、刪除外鍵、修改外鍵等，在后面的小節中介紹。

4.3.2 創建外鍵約束

外鍵可以在MySQL Workbench中進行創建，也可以使用SQL語句進行創建。外鍵約束的添加不同于主鍵約束，因為外鍵是作用在多個表的基礎上。

1．MySQL Workbench添加外鍵

外鍵是設計在兩個表之間的，一個表的外鍵大多是另一個表的主鍵。如水果信息表中有水果負責人編號字段，該字段對應職員信息表中的職員編號（主鍵）字段。那么對水果信息表設置主鍵時，除了將水果負責人字段添加外鍵約束，同時還需要指出該字段所對應的外表字段和字段所在的外表。

【范例5】

向水果信息表fruits中添加外鍵約束，其中fwid對應work表中的wid字段，步驟如下。

（1）在表fruits名稱處右擊，選擇Alter Table選項打開表的修改界面。在表修改界面的下方，單擊Foreign Keys選項，如圖4-3所示。

（2）如圖4-3所示，該窗體分為多個區域，在左下方為需要引用的外鍵名稱和需要引用的表，而下部中間位置，是fruits表中的字段和需要引用的字段。在左下方區域中是一個表格，第一列是水果信息表中的外鍵約束的名稱，第二列是該外鍵所需要引用的表。

外鍵約束的名稱可以自由定義，如fwid字段對應work表中的wid字段，那么外鍵名稱可以定義為fwid_wid。而外鍵所引用的表必須在該數據庫中選擇，第二列是以下拉框的形式列出了當前數據庫中所有的表。

（3）在界面下部的中間區域中也是一個表格，第一列是水果信息表中的所有字段；第二列以下拉框的形式列出外表中的字段。

如圖4-3左側設置了約束名稱是fwid_wid，對應的外表是work表，那么在中間區域第二列的下拉框中將列舉work表的字段列表。在第一列選中fruits表的fwid字段；在第二列中選中對應的wid字段即可。

（4）如圖4-3所示，在該窗體的右下方區域，需要選擇外鍵約束選項，在控制臺中若不聲明該選項，則默認是采用RESTRICT方式。對于外鍵，最好是采用ON UPDATE CASCADE和ON DELETE RESTRICT的方式。

外鍵約束選項用于表數據修改和刪除時，各個關鍵表中關聯數據的處理。其各選項的作用如下所示。

①CASCADE：外鍵表中外鍵字段值會跟隨父表被更新，或所在的列會被刪除。

②NO ACTION：不進行任何關聯操作。

③RESTRICT：RESTRICT相當于NO ACTION，即不進行任何操作。拒絕父表修改外鍵關聯列，刪除記錄。

④SET NULL：在父表的外鍵關聯字段被修改和刪除時，外鍵表的外鍵列被設置為空（NULL）。

而對于數據的添加，子表的外鍵列輸入的值，只能是父表外鍵關聯列已有的值，否則出錯。

（5）單擊Apply按鈕打開執行對話框；接著單擊Apply按鈕確認執行SQL語句；最后在彈出的對話框中單擊Finish按鈕完成數據表的修改。

范例5是在現有的表中添加外鍵約束，對于新建的表創建外鍵約束，只需要添加好字段，接著選擇界面下方的Foreign Keys選項設置外鍵，外鍵的設置步驟與范例5的步驟一樣。

2．SQL語句添加外鍵

添加外鍵有兩種方式，一種是在創建表的時候添加外鍵；一種是在現有的表中添加外鍵約束。其語法如下所示：

INDEX '索引名' ('外鍵字段' ASC),
    CONSTRAINT '外鍵約束名'
      FOREIGN KEY ('外鍵字段')
      REFERENCES '外表名稱' ('外表對應字段')
      約束選項;

上述代碼是創建表的時候，在創建語句之后添加的語句。可以放在字段列表和主鍵約束定義之后。

【范例6】

創建ftype表，有tid、fid、title和type字段。為fid字段設置外鍵約束名稱為fruit，對應外表fruits表中的fid字段，代碼如下。

CREATE TABLE 'fruitshop'.'ftype' (
      'tid' INT NOT NULL,
      'fid' INT NULL,
      'title' VARCHAR(45) NULL,
      'type' VARCHAR(45) NULL,
      PRIMARY KEY ('tid'),
      INDEX 'fruit_idx' ('fid' ASC),
      CONSTRAINT 'fruit'
        FOREIGN KEY ('fid')
        REFERENCES 'fruitshop'.'fruits' ('fid')
        ON DELETE NO ACTION
        ON UPDATE NO ACTION);

范例6是一個在創建表時設置外鍵的例子。若是對現有的表添加外鍵約束，需要在CONSTRAINT關鍵字前添加ADD關鍵字。

【范例7】

對現有的fruits表進行修改，為fwid字段添加外鍵約束，對應work表中的wid字段，代碼如下。

ALTER TABLE 'fruitshop'.'fruits'
    ADD CONSTRAINT 'fwid_wid'
      FOREIGN KEY ('fwid')
      REFERENCES 'fruitshop'.'work' ('wid')
      ON DELETE NO ACTION
      ON UPDATE NO ACTION;

上述代碼是在fwid字段有索引的情況下執行的，若fwid字段沒有索引，需要首先為其添加索引，代碼如下。

ADD INDEX ' fwid_wid_idx' ('fwid' ASC),

其中，ASC表示數據按照從小到大的順序排序，是定義索引時需要定義的排序方式，另有DESC表示數據按照從大到小的順序排序。

MySQL對創建外鍵要求比較嚴格，精確到字段的類型和長度，在創建時需要注意以下幾點。

（1）字段名及其對應的數據表名稱不能有誤。

（2）字段類型必須對應。

（3）字段的數據值必須對應。這里要求，外鍵字段的值在外表所對應的字段中必須存在，否則將創建失敗。如范例7中，fruits表fwid字段的值，在work表中的wid字段中必須有相同數據值來對應。

（4）檢查字段的約束，除了主鍵約束和外鍵約束以外，其他約束必須一致。

（5）檢查字符集，為了遷移和使用的方便，盡量使用utf8字符集。

4.3.3 修改外鍵約束

修改外鍵約束包括多種情況，如修改當前外鍵約束的字段所對應的字段、修改當前表的外鍵字段等。外鍵約束的修改可以在MySQL Workbench中進行，也可以使用SQL語句進行。使用MySQL Workbench進行修改的方法與表修改的方法一樣，對外鍵的設置可參考創建外鍵時的設置。

在修改外鍵之前可使用SHOW CREATE TABLE語句查看表中已有的外鍵設置，如查看fruitshop.fruits表的外鍵設置，代碼如下。

SHOW CREATE TABLE 'fruitshop'.'fruits' ;

上述代碼的執行效果如下所示。

+ ------------- + ------------------------- +
    | Table           | Create Table                |
    + ------------- + ------------------------- +
    | fruits          | CREATE TABLE 'fruits' (
      'fid' int(11) NOT NULL,
      'fname' varchar(45) DEFAULT NULL,
      'fprice' varchar(45) DEFAULT NULL,
      'ftime' varchar(45) DEFAULT NULL,
      'fwid' int(11) DEFAULT NULL,
      PRIMARY KEY ('fid'),
      KEY 'wid_idx' ('fwid'),
      CONSTRAINT 'fwid_wid' FOREIGN KEY ('fwid') REFERENCES 'work' ('wid') ON   DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    + ------------- + -------------------- +
    1 rows

使用SQL語句對外鍵約束進行修改，需要首先刪除表中已有的外鍵，再添加新的外鍵約束來代替。

【范例8】

修改fruits表的外鍵設置，使fwid字段的外鍵約束對應workers表中的id字段，代碼如下。

ALTER TABLE 'fruitshop'.'fruits'
    DROP FOREIGN KEY 'fwid_wid';
    ALTER TABLE 'fruitshop'.'fruits'
    ADD CONSTRAINT 'fwid_wid'
      FOREIGN KEY ('fwid')
      REFERENCES 'fruitshop'.'workers' ('id')
      ON DELETE RESTRICT
      ON UPDATE RESTRICT;

上述代碼可分為兩部分，前兩條語句是刪除了當前的外鍵；后面的語句是添加新的外鍵，語法與范例7中添加外鍵的語法一樣。