全部教學 » MySQL外來鍵約束

MySQL外來鍵約束

2019-10-16 22:57:14

在本教學中，您將了解MySQL外來鍵(foreign key)以及如何在MySQL中建立，新增和刪除外來鍵約束。

MySQL外來鍵簡介

外來鍵表示一個表中的一個欄位被另一個表中的一個欄位參照。外來鍵對相關表中的資料造成了限制，使MySQL能夠保持參照完整性。

下面來看看範例資料庫(yiibaidb)中的以下資料庫中兩個表：customers和`orders``的ER圖。

上圖中有兩張表：customers和orders。每個客戶有零個或多個訂單，每個訂單只屬於一個客戶。customers表和orders表之間的關係是一對多的，它是由customerNumber欄位指定在orders表中建立外來鍵(參照customers表的customerNumber欄位)。orders表中的customerNumber欄位與customers表中的customerNumber主鍵欄位相關。

customers表稱為父表或參照表，orders表稱為子表或參照表。

表可以有多個外來鍵，子表中的每個外來鍵可能參照不同的父表。

子表中的行必須包含父表中存在的值，例如，orders表中的每個訂單記錄必須在customers表中存在customerNumber。 因此，多個訂單可以指同一個客戶，因此這種關係稱為一個(客戶)到許多(訂單)或一對多。

有時，子表和父表是一樣的。外來鍵返回到表的主鍵，例如以下employees表：

reportTo列是一個參照employeeNumber列的外來鍵，employeeNumber列是employees表的主鍵，以反映員工之間的報告結構，即每個員工向另一個員工傳送的報告和員工可以有零個或多個直接報告。有關如何使用有，請參考自連線教學，以幫助您根據這種表查詢來查詢相關資料。

reportTo外來鍵也稱為遞回或自參照外來鍵。

外來鍵執行參照完整性，可以幫助您自動維護資料的一致性和完整性。 例如，不能為不存在的客戶建立訂單。

此外，可以為customerNumber外來鍵設定級聯刪除操作，以便在customers表中刪除客戶時，與客戶關聯的所有訂單也將被刪除。 這樣可以節省您使用多個DELETE語句或DELETE JOIN語句的時間和精力。

與刪除相同，還可以為customerNumber外來鍵定義級聯更新操作，以執行交叉表更新，而不使用多個UPDATE語句或UPDATE JOIN語句。

在MySQL中，InnoDB儲存引擎支援外來鍵，因此您必須建立InnoDB表才能使用外來鍵約束。

建立外來鍵

MySQL建立外來鍵語法

以下語法說明了如何在CREATE TABLE語句中的子表中定義外來鍵。

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

下面我們來更詳細的檢視上面語法：

  • CONSTRAINT子句允許您為外來鍵約束定義約束名稱。如果省略它，MySQL將自動生成一個名稱。
  • FOREIGN KEY子句指定子表中參照父表中主鍵列的列。您可以在FOREIGN KEY子句後放置一個外來鍵名稱，或者讓MySQL為您建立一個名稱。 請注意，MySQL會自動建立一個具有foreign_key_name名稱的索引。
  • REFERENCES子句指定父表及其子表中列的參照。 在FOREIGN KEY和REFERENCES中指定的子錶和父表中的列數必須相同。
  • ON DELETE子句允許定義當父表中的記錄被刪除時，子表的記錄怎樣執行操作。如果省略ON DELETE子句並刪除父表中的記錄，則MySQL將拒絕刪除子表中相關聯的資料。此外，MySQL還提供了一些操作，以便您可以使用其他選項，例如ON DELETE CASCADE，當刪除父表中的記錄時，MySQL可以刪除子表中參照父表中記錄的記錄。 如果您不希望刪除子表中的相關記錄，請改用ON DELETE SET NULL操作。當父表中的記錄被刪除時，MySQL會將子表中的外來鍵列值設定為NULL，條件是子表中的外來鍵列必須接受NULL值。 請注意，如果使用ON DELETE NO ACTION或ON DELETE RESTRICT操作，MySQL將拒絕刪除。
  • ON UPDATE子句允許指定在父表中的行更新時，子表中的行會怎樣執行操作。當父表中的行被更新時，可以省略ON UPDATE子句讓MySQL拒絕對子表中的行的任何更新。 ON UPDATE CASCADE操作允許您執行交叉表更新，並且當更新父表中的行時，ON UPDATE SET NULL操作會將子表中行中的值重置為NULL值。 ON UPDATE NO ACTION或UPDATE RESTRICT操作拒絕任何更新。

MySQL建立表外來鍵範例

以下範例建立一個dbdemo資料庫和兩個表：categories和products。每個類別都有一個或多個產品，每個產品只屬於一個類別。 products表中的cat_id欄位被定義為具有UPDATE ON CASCADE和DELETE ON RESTRICT操作的外來鍵。

CREATE DATABASE IF NOT EXISTS dbdemo;

USE dbdemo;

CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;

CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

新增外來鍵

MySQL新增外來鍵語法

要將外來鍵新增到現有表中，請使用ALTER TABLE語句與上述外來鍵定義語法：

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action;

MySQL新增外來鍵範例

現在，我們新增一個名為vendors的新表，並更改products表以包含供應商ID欄位：

USE dbdemo;

CREATE TABLE vendors(
    vdr_id int not null auto_increment primary key,
    vdr_name varchar(255)
)ENGINE=InnoDB;

ALTER TABLE products 
ADD COLUMN vdr_id int not null AFTER cat_id;

要在products表中新增外來鍵，請使用以下語句：

ALTER TABLE products
ADD FOREIGN KEY fk_vendor(vdr_id)
REFERENCES vendors(vdr_id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

現在，products表有兩個外來鍵，一個是參照categories表，另一個是參照vendors表。

刪除MySQL外來鍵

您還可以使用ALTER TABLE語句將外來鍵刪除，如下語句：

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

在上面的宣告中：

  • 首先，指定要從中刪除外來鍵的表名稱。
  • 其次，將約束名稱放在DROP FOREIGN KEY子句之後。

請注意，constraint_name是在建立或新增外來鍵到表時指定的約束的名稱。 如果省略它，MySQL會為您生成約束名稱。

要獲取生成的表的約束名稱，請使用SHOW CREATE TABLE語句，如下所示：

SHOW CREATE TABLE table_name;

例如，要檢視products表的外來鍵，請使用以下語句：

SHOW CREATE TABLE products;

以下是語句的輸出：

CREATE TABLE products (
  prd_id int(11) NOT NULL AUTO_INCREMENT,
  prd_name varchar(355) NOT NULL,
  prd_price decimal(10,0) DEFAULT NULL,
  cat_id int(11) NOT NULL,
  vdr_id int(11) NOT NULL,
  PRIMARY KEY (prd_id),
  KEY fk_cat (cat_id),
  KEY fk_vendor(vdr_id),

  CONSTRAINT products_ibfk_2 
  FOREIGN KEY (vdr_id) 
  REFERENCES vendors (vdr_id) 
  ON DELETE NO ACTION 
  ON UPDATE CASCADE,

  CONSTRAINT products_ibfk_1 
  FOREIGN KEY (cat_id) 
  REFERENCES categories (cat_id) 
  ON UPDATE CASCADE
) ENGINE=InnoDB;

products表有兩個外來鍵約束：products_ibfk_1和products_ibfk_2。

可以使用以下語句刪除products表的外來鍵：

ALTER TABLE products 
DROP FOREIGN KEY products_ibfk_1;

ALTER TABLE products 
DROP FOREIGN KEY products_ibfk_2;

MySQL禁用外來鍵檢查

有時，因為某種原因需要禁用外來鍵檢查(例如將CSV檔案中的資料匯入表中)非常有用。 如果不禁用外來鍵檢查，則必須以正確的順序載入資料，即必須首先將資料載入到父表中，然後再將資料載入匯入到子表中，這可能是乏味的。 但是，如果禁用外來鍵檢查，則可以按任何順序載入匯入資料。

除非禁用外來鍵檢查，否則不能刪除由外來鍵約束參照的表。刪除表時，還會刪除為表定義的任何約束。

要禁用外來鍵檢查，請使用以下語句：

SET foreign_key_checks = 0;

當然，可以使用以下語句啟用它：

SET foreign_key_checks = 1;

在本教學中，我們已經介紹了很多有關MySQL外來鍵的內容。還向您介紹了一些非常方便的語句，允許您在MySQL中有效地管理外來鍵。