如何在 SQL 中创建外键约束

作者
Tweet
Share
Share
Pin
0 Shares

數據庫中約束的重要性：主鍵與外鍵

對於任何企業而言，數據的價值和需求都是至關重要的。擁有儲存和管理數據庫的能力可以為企業帶來巨大的增值。數據庫通過特定的規則進行組織，並允許您在連接中建立數據關係。其中，關係型數據庫自1970年代以來便被廣泛接受為一種主流的數據管理方式。由於其強大的數據處理能力，關係型數據庫在當今的市場中仍然備受青睞。

根據Statista的數據顯示，截至2022年1月，儘管市面上有眾多關係型數據庫可供選擇，MySQL仍穩居全球第二。在SQL服務器中，「約束」是在單個或多個列中強制執行的預定義規則和限制。這些約束與列中的數據值相關聯，有助於確保指定列數據的完整性、準確性和可靠性。簡而言之，只有滿足約束規則的數據才能成功插入到列中；若數據不符合條件，插入操作將會終止。

本文假設您已對關係型數據庫，特別是MySQL，有所了解，並希望進一步提升在此領域的知識。最後，我將分享一些關於如何有效使用外鍵約束的技巧。

主鍵約束回顧

在SQL中，表包含一個或多個列，這些列保存著可以精確定位系統中每一行的鍵值。表中被指定為「主鍵」（PK）的一列或多列，其作用是確保表的實體完整性。主鍵約束保證數據的唯一性，通常在標識列上定義。當您為表指定主鍵約束後，數據庫引擎會自動為每個主列建立唯一索引，從而強制數據的唯一性。通過提供快速的數據訪問，當主鍵被用於查詢時，能帶來極大的效率提升。如果主鍵約束定義在多個列上，則稱為複合主鍵或組合主鍵。在這種情況下，每個主鍵列都可能包含重複的值，但主鍵中所有列的組合值必須是唯一的。

舉例來說，假設您有一個包含「id」、「names」和「age」列的表。當您在`id`和`names`的組合上定義主鍵約束時，`id`或`names`的值可以重複。然而，為了避免重複的行，每個組合必須是唯一的。因此，您可能會有「id=1」和「name=Walter」、「age=22」以及「id=1」、「name=Henry」和「age=27」的記錄，但不能存在使用`id=1`和`name=Walter`的另一筆記錄，因為這個組合並非唯一。

以下是關於主鍵約束的一些重要知識點：

  • 一個表只能包含一個主鍵約束。
  • 主鍵不能超過16個列，最大長度為900個字符。
  • 主鍵生成的索引會增加表中的索引。但是，一個表只能有一個叢集索引，而非叢集索引的數量限制為999個。
  • 當沒有為鍵約束指定叢集或非叢集索引時，將自動採用叢集索引。
  • 主鍵約束中聲明的所有列都應該被定義為非空。如果不是這種情況，則約束中所有列的可空性會自動設定為非空。
  • 當在公共語言運行時（CLR）的用戶定義列類型上定義主鍵時，該類型實作必須支援二進制排序。

外鍵約束概述

外鍵（FK）利用一個或多個列的組合來建立和連結兩個表之間的關係，並管理要儲存在外鍵表中的數據。外鍵參考需要在兩個表之間建立連接；當一個表中保存著另一個表的主鍵的一個或多個列被另一個表中的一個或多個列引用時，就會發生這種情況。在實際應用中，可以有一個Sales.SalesOrderHeader表，其外鍵連結到另一個Sales.Person表，因為銷售人員和銷售訂單之間存在邏輯關係。在這裡，SalesOrderHeader列中的SalesPersonID與SalesPerson表的主鍵列相對應。SalesPerson表的外鍵實際上是SalesOrderHeader表中的SalesPersonID列。此關係定義了一條規則：如果SalesPersonID值不存在於SalesPerson表中，則該值不能出現在您的SalesOrderHeader表中。

一個表最多可以引用253個其他列和表作為外鍵，這被稱為傳出參考。自2016年起，SQL Server將單個表中可以引用的表和列的數量（也稱為傳入參考）從253增加到了10000。但這種增加存在一些限制：

  • 超過253的外鍵參考僅適用於DELETE DML操作。不支援MERGE和UPDATE操作。
  • 外鍵參考自身的表最多有253個外鍵參考。
  • 對於列存儲索引、記憶體優化表和分割外鍵表，外鍵參考限制為253個。

外鍵的好處是什麼？

如前所述，外鍵約束在維護關係數據庫的完整性和數據一致性方面起著至關重要的作用。以下是外鍵約束至關重要的原因分析：

  • 引用完整性：外鍵約束確保每個子表記錄都對應一個主表記錄，保證了兩個表之間的數據一致性。
  • 防止孤立記錄：如果父表被刪除，外鍵約束可確保關聯的子表也被刪除，從而防止產生可能導致數據不一致的孤立記錄。
  • 提高性能：外鍵約束允許數據庫管理系統根據表關係優化查詢，從而提高查詢效率。

外鍵約束索引

外鍵約束不會像主索引那樣自動建立相應的索引。但可以為外鍵約束手動建立索引，這對性能提升是有益的。主要原因如下：

  • 在外鍵列通常用於連接條件時，通過匹配綁定到約束的列來組合查詢中相關表的數據。索引幫助數據庫在外部表中快速查找相關數據。
  • 如果您變更主鍵約束，系統會檢查相關表中的外部約束。

建立索引並非強制要求。您仍然可以在不指定主鍵和外鍵約束的情況下組合兩個表中的數據。然而，添加外鍵約束可以優化表，並將它們組合在滿足其使用鍵標準的查詢中。如果您變更主鍵約束，系統會檢查相關的外部約束。

在SQL中建立外鍵約束的技巧

經過以上的理論探討，現在讓我們將注意力轉移到實際操作上，來學習如何建立外鍵約束。表中的「外鍵」字段是指向另一個表的「主鍵」。擁有主鍵的表是父表，而帶有外鍵的表則被稱為子表。讓我們開始吧。

在建立表的同時建立外鍵

在建立表時，您也可以同時建立外鍵約束以維護參照完整性。方法如下：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

以上程式碼建立了一個名為「orders」的表，其中包括主整數鍵「order_id」，另一個整數「customer_id」，以及日期「order_date」。在這種情況下，外鍵約束被添加到「customer_id」列，並參考「customers」表中的「customer_id」。

在建立表後建立外鍵

假設您已經建立了一個表，現在想要添加外鍵約束，可以使用`ALTER TABLE`語句。請參考以下程式碼片段：

ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

在此範例中，您在「orders」表中添加了一個外鍵約束，該約束將「customer_id」列連結至「customers」表中的「customer_id」列。

在不檢查現有數據的情況下建立外鍵

當您向表添加外鍵約束時，數據庫會自動檢查現有數據，以確保與約束的一致性。但是，如果您確定數據是一致的，並且希望在不進行一致性檢查的情況下添加約束，可以依照以下方法進行操作：

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
NOT VALIDATE;

NOT VALIDATE命令指示數據庫不要檢查現有數據。在特定情況下，這個方法很有幫助。例如，當您有大量數據且希望加快驗證過程時。

通過 DELETE/UPDATE 建立外鍵

當您建立外鍵約束時，您可以指定在更新或刪除參考行時應採取的操作。在這種情況下，您可以使用級聯引用完整性約束來指示要執行的操作。這些操作包括：

#1. 無操作

與許多其他數據庫一樣，「無操作」規則是您建立外鍵約束時的預設行為。這表示在刪除或更新參考行時，不會執行任何操作。如果違反了外鍵約束，數據庫引擎會拋出錯誤。但是，並不建議這樣做，因為這可能會導致引用完整性問題，因此強烈建議強制執行外鍵約束。以下是如何進行操作的範例：

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

#2. 級聯

「CASCADE」規則是在建立外鍵約束時，對於「ON DELETE」和「ON UPDATE」操作的另一個選項。當設定為級聯時，表示每當父表中的一行被更新或刪除時，所有參考的行也會相應地被更新或刪除。在維護引用完整性時，這個技術非常強大。以下是一個範例：

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

使用這個規則時應該特別小心，因為如果不慎使用可能會導致不良後果。您需要避免意外刪除過多數據或產生循環參考。因此，僅在必要時謹慎使用此選項。使用「CASCADE」規則有一些注意事項：

  • 如果時間戳列是外鍵或參考鍵的一部分，則不能指定「CASCADE」。
  • 如果您的表有「INSTEAD OF DELETE」觸發器，則不能指定「ON DELETE CASCADE」。
  • 如果您的表有「INSTEAD OF UPDATE」觸發器，則不能指定「ON UPDATE CASCADE」。

#3. 設為NULL

當您刪除或更新父表中的相應行時，所有構成外鍵的值都將設為NULL。此約束規則要求外鍵列必須可以為NULL才能執行，且不能為具有「INSTEAD OF UPDATE」觸發器的表指定。以下是一個操作範例：

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET NULL
ON UPDATE SET NULL

在這個例子中，如果刪除或更新了「customers」表中的相應行，則「orders」表中的外鍵列「customer_id」將被設定為NULL。

#4. 設定預設值

在這裡，您可以設定外鍵為預設值，前提是父表中的參考行被更新或刪除。只有當所有外鍵列都具有預設值時，此約束才會被執行。如果列可以為NULL，則其預設值將被設定為NULL。請注意，不能為具有「INSTEAD OF UPDATE」觸發器的表指定這個選項。以下是一個範例：

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT;

在上述情況下，您已將「orders」表中的「customer_id」設定為其預設值，當「customers」表中的相應行被刪除或更新時，將會觸發此設定。

總結

在本指南中，您複習了主鍵約束，並深入了解了外鍵約束。您還學習了幾種建立外鍵約束的技術。雖然建立外鍵約束的方法有很多，但本文重點說明了這些方法。

希望您掌握了新的技術。您不僅可以組合使用這些技術。例如，可以在具有參考關係的表上組合使用「CASCADE」、「SET NULL」、「SET DEFAULT」和「NO ACTION」約束。如果您的表遇到「NO ACTION」約束，則會回溯到其他約束規則。在其他情況下，「DELETE」操作可以觸發這些規則的組合，「NO ACTION」規則將作為最後一個執行。

接下來，請參考SQL備忘單。