建立外部索引鍵關聯性

  • 發行項

適用於：SQL Server 2016 (13.x) 和更新版本 Azure SQL DatabaseAzure SQL 受控執行個體

本文描述如何使用 SQL Server Management Studio 或 Transact-SQL，在 SQL Server 中建立外部索引鍵關聯性。 當想要將一個資料表的資料列，與其他資料表的資料列建立相關時，可以建立兩者間的關聯性。

權限

建立具有外部索引鍵的新資料表，需要資料庫中的 CREATE TABLE 權限及建立資料表的結構描述之 ALTER 權限。

在現有資料表中建立外部索引鍵需要此資料表的 ALTER 權限。

限制事項

  • 外部索引鍵條件約束不一定只能連結到另一個資料表中的主索引鍵條件約束。 外部索引鍵也可以定義成參考另一個資料表中 UNIQUE 條件約束的資料行。

  • 在 FOREIGN KEY 條件約束的資料行中輸入 NULL 以外的值時，值必須在參考的資料行中。 否則，系統會傳回外部索引鍵違規錯誤訊息。 若要確定會驗證複合外部索引鍵條件約束的所有值，請對所有參與的資料行指定 NOT NULL。

  • FOREIGN KEY 條件約束只能參考在相同伺服器之相同資料庫內的資料表。 跨資料庫參考完整性必須利用觸發程序來實作。 如需詳細資訊，請參閱 CREATE TRIGGER。

  • FOREIGN KEY 條件約束可以參考相同資料表中的另一個資料行，這稱為自我參考。

  • 資料行層級上指定的 FOREIGN KEY 條件約束只能列出一個參考資料行。 這個資料行必須有定義了條件約束的資料行之相同資料類型。

  • 資料表層級上指定的 FOREIGN KEY 條件約束，必須有與條件約束資料行清單中資料行一樣多的參考資料行。 每個參考資料行的資料類型，也必須與資料行清單中的對應資料行相同。

  • 資料庫引擎沒有預先定義的限制，來限制一個資料表可以包含多少個 FOREIGN KEY 條件約束參考其他資料表。 資料庫引擎也不會限制其他資料表只能擁有多少個 FOREIGN KEY 條件約束參考特定資料表。 不過，FOREIGN KEY 條件約束的實際可用數目，會受到硬體設定及資料庫與應用程式設計的限制。 一個資料表最多可以參考其他 253 個資料表和資料行作為外部索引鍵 (連出參考)。 SQL Server 2016 (13.x) 和更新版本會對多少個其他資料表和資料行可以參考單一資料表中的資料行增加限制 (連入參考)，從 253 提高至 10,000。 (至少需要 130 相容性層級)。此增加具有下列限制：

    • DELETE 和 UPDATE DML 作業支援大於 253 的外部索引鍵參考數目。 但是，不支援 MERGE 作業。
    • 如果資料表具有參考本身的外部索引鍵，則仍會限制為 253 個外部索引鍵參考。
    • 目前，253 個以上的外部索引鍵參考數目不適用於資料行存放區索引、記憶體最佳化資料或 Stretch Database。

    重要

    Stretch Database 在 SQL Server 2022 (16.x) 及 Azure SQL 資料庫中已被取代。 資料庫引擎的未來版本將移除此功能。 請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。

  • 暫存資料表不會強制執行 FOREIGN KEY 條件約束。

  • 如果在 CLR 使用者定義的類型資料行上定義外部索引鍵，類型的實作必須支援二進位順序。 如需詳細資訊，請參閱 CLR 使用者定義型別。

  • 只有在所參考的主索引鍵也定義成 varchar(max) 類型時， varchar(max) 類型的資料行才能夠參與外部索引鍵條件約束。

在資料表設計工具建立外部索引鍵關聯性

使用 SQL Server Management Studio

  1. 在物件總管中，以滑鼠右鍵按一下位於關聯性外部索引鍵端上的資料表，然後選取 [設計]。

    資料表會在資料表設計工具中開啟。

  2. 從 [資料表設計工具] 功能表中，選取 [關聯性]。 (請查看標頭中的 [資料表設計工具] 功能表，或以滑鼠右鍵按一下資料表定義的空白處，然後選取 [關聯性...]。)

  3. 在 [外部索引鍵關聯性] 對話方塊中，選取 [新增]。

    關聯性會出現在 [選取的關聯性] 清單中，並顯示系統提供的名稱，格式為 FK_<tablename>_<tablename>，其中第一個 tablename 為外部索引鍵資料表的名稱，而第二個 tablename 為主索引鍵資料表的名稱。 這只是外部索引鍵物件 (Name) 欄位的預設和一般命名慣例。

  4. 在 [選取的關聯性] 清單中，選取關聯性。

  5. 選取方格右邊的 [資料表及資料行規格]，然後選取屬性右邊的省略符號 (...)。

  6. 在 [資料表和資料行] 對話視窗的 [主索引鍵] 下拉式清單中，選擇將要成為關聯性主索引鍵端的資料表。

  7. 在方格的下方，選擇組成資料表主索引鍵的資料行。 在每個資料行右側的鄰近方格資料格，選擇對應到外部索引鍵資料表的外部索引鍵資料行。

    [資料表設計工具] 會提供關聯性的建議名稱。 若要變更這個名稱，請編輯 [關聯性名稱] 文字方塊的內容。

  8. 選擇 [確定] 建立關聯性。

  9. 關閉資料表設計工具視窗，並儲存您的變更，讓外部索引鍵關聯性變更生效。

在新的資料表建立外部索引鍵

使用 Transact-SQL

下列範例會建立資料表並在 TempID 資料行上定義外部索引鍵條件約束，而此資料行會參考 AdventureWorks 資料庫中 Sales.SalesReason 資料表內的 SalesReasonID 資料行。 ON DELETE CASCADE 和 ON UPDATE CASCADE 子句用來確定對 Sales.SalesReason 資料表所做的變更會自動傳播至 Sales.TempSalesReason 資料表。

CREATE TABLE Sales.TempSalesReason 
   (
      TempID int NOT NULL, Name nvarchar(50)
      , CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID)
      , CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
        REFERENCES Sales.SalesReason (SalesReasonID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
   )
;

在現有的資料表建立外部索引鍵

使用 Transact-SQL

下列範例會在 TempID 資料行上建立外部索引鍵，並參考 AdventureWorks 資料庫中 Sales.SalesReason 資料表內的 SalesReasonID 資料行。

ALTER TABLE Sales.TempSalesReason
   ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
      REFERENCES Sales.SalesReason (SalesReasonID)
      ON DELETE CASCADE
      ON UPDATE CASCADE
;

下一步