建立外部索引鍵關聯性Create Foreign Key Relationships

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

此文章說明如何使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL,在 SQL Server 2017SQL Server 2017 中建立外部索引鍵關聯性。This article describes how to create foreign key relationships in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 當想要將一個資料表的資料列,與其他資料表的資料列建立相關時,可以建立兩者間的關聯性。You create a relationship between two tables when you want to associate rows of one table with rows of another.

開始之前!Before You Begin! 限制事項Limits and Restrictions

  • 外部索引鍵條件約束不一定只能連結到另一個資料表中的主索引鍵條件約束;它也可以定義成參考另一個資料表中 UNIQUE 條件約束的資料行。A foreign key constraint does not have to be linked only to a primary key constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

  • 在 FOREIGN KEY 條件約束的資料行中輸入 NULL 以外的值時,值必須在參考的資料行中;否則,系統會傳回外部索引鍵違規錯誤訊息。When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned. 若要確定會驗證複合外部索引鍵條件約束的所有值,請對所有參與的資料行指定 NOT NULL。To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.

  • FOREIGN KEY 條件約束只能參考在相同伺服器之相同資料庫內的資料表。FOREIGN KEY constraints can reference only tables within the same database on the same server. 跨資料庫參考完整性必須利用觸發程序來實作。Cross-database referential integrity must be implemented through triggers. 如需詳細資訊,請參閱 CREATE TRIGGERFor more information, see CREATE TRIGGER.

  • FOREIGN KEY 條件約束可以參考相同資料表中的另一個資料行。FOREIGN KEY constraints can reference another column in the same table. 這稱為自我參考。This is referred to as a self-reference.

  • 資料行層級上指定的 FOREIGN KEY 條件約束只能列出一個參考資料行。A FOREIGN KEY constraint specified at the column level can list only one reference column. 這個資料行必須有定義了條件約束的資料行之相同資料類型。This column must have the same data type as the column on which the constraint is defined.

  • 資料表層級上指定的 FOREIGN KEY 條件約束,必須有與條件約束資料行清單中資料行一樣多的參考資料行。A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. 每個參考資料行的資料類型,也必須與資料行清單中的對應資料行相同。The data type of each reference column must also be the same as the corresponding column in the column list.

  • 在資料表所能包含參考其他資料表的 FOREIGN KEY 條件約束數目,及其他資料表所擁有參考特定資料表的 FOREIGN KEY 條件約束數目上, Database EngineDatabase Engine 並沒有預先定義的限制。The Database EngineDatabase Engine does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain that reference other tables, or the number of FOREIGN KEY constraints that are owned by other tables that reference a specific table. 不過,FOREIGN KEY 條件約束的實際可用數目,會受到硬體組態及資料庫和應用程式設計的限制。Nevertheless, the actual number of FOREIGN KEY constraints that can be used is limited by the hardware configuration and by the design of the database and application. 一個資料表最多可以參考其他 253 個資料表和資料行作為外部索引鍵 (連出參考)。A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x)SQL Server 2016 (13.x) 將單一資料表中資料行可以參考的其他資料表和資料行數目限制 (連入參考) 從 253 提高至 10,000。increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (至少需要 130 相容性層級)。此增加具有下列限制:(Requires at least 130 compatibility level.) The increase has the following restrictions:

    • DELETE 和 UPDATE DML 作業支援大於 253 的外部索引鍵參考數目。Greater than 253 foreign key references are supported for DELETE and UPDATE DML operations. 但是,不支援 MERGE 作業。MERGE operations are not supported.
    • 如果資料表具有參考本身的外部索引鍵,則仍會限制為 253 個外部索引鍵參考。A table with a foreign key reference to itself is still limited to 253 foreign key references.
    • 目前,253 個以上的外部索引鍵參考數目不適用於資料行存放區索引、記憶體最佳化資料或 Stretch Database。Greater than 253 foreign key references are not currently available for columnstore indexes, memory-optimized tables, or Stretch Database.
  • 暫存資料表不會強制執行 FOREIGN KEY 條件約束。FOREIGN KEY constraints are not enforced on temporary tables.

  • 如果在 CLR 使用者定義的類型資料行上定義外部索引鍵,類型的實作必須支援二進位順序。If a foreign key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering. 如需詳細資訊,請參閱 CLR 使用者定義型別For more information, see CLR User-Defined Types.

  • 只有在所參考的主索引鍵也定義成 varchar(max) 類型時, varchar(max) 類型的資料行才能夠參與外部索引鍵條件約束。A column of type varchar(max) can participate in a FOREIGN KEY constraint only if the primary key it references is also defined as type varchar(max).

權限Permissions

建立具有外部索引鍵的新資料表,需要資料庫中的 CREATE TABLE 權限及建立資料表的結構描述之 ALTER 權限。Creating a new table with a foreign key requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

在現有資料表中建立外部索引鍵需要此資料表的 ALTER 權限。Creating a foreign key in an existing table requires ALTER permission on the table.

在資料表設計工具建立外部索引鍵關聯性Create a foreign key relationship in Table Designer

使用 SQL Server Management StudioUsing SQL Server Management Studio

  1. 在物件總管中,以滑鼠右鍵按一下位於關聯性之外部索引鍵端上的資料表,然後按一下 [設計] 。In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and click Design.

    資料表會在 [資料表設計工具] 中開啟。The table opens in Table Designer.

  2. [資料表設計工具] 功能表中,按一下 [關聯性]From the Table Designer menu, click Relationships.

  3. 在 [外部索引鍵關聯性] 對話方塊中,按一下 [加入] 。In the Foreign-key Relationships dialog box, click Add.

    關聯性會出現在 [選取的關聯性] 清單中,並顯示系統提供的名稱,格式為 FK_<資料表名稱> ><資料表名稱 >,其中<資料表名稱> 為外部索引鍵資料表的名稱。The relationship appears in the Selected Relationship list with a system-provided name in the format FK<tablename>_<tablename>, where tablename is the name of the foreign key table.

  4. 在 [ 選取的關聯性 ] 清單中,按一下關聯性。Click the relationship in the Selected Relationship list.

  5. 按一下方格右邊的 [資料表及資料行規格] ,然後按一下屬性右邊的省略符號 ( ... )。Click Tables and Columns Specification in the grid to the right and click the ellipses (...) to the right of the property.

  6. 在 [資料表和資料行] 對話視窗的 [主索引鍵] 下拉式清單中,選擇將要成為關聯性主索引鍵端的資料表。In the Tables and Columns dialog box, in the Primary Key drop-down list, choose the table that will be on the primary-key side of the relationship.

  7. 在方格的下方,選擇組成資料表主索引鍵的資料行。In the grid beneath, choose the columns contributing to the table's primary key. 在每個資料行左側的鄰近方格資料格,選擇對應到外部索引鍵資料表的外部索引鍵資料行。In the adjacent grid cell to the left of each column, choose the corresponding foreign-key column of the foreign-key table.

    [資料表設計工具] 會提供關聯性的建議名稱。Table Designer suggests a name for the relationship. 若要變更這個名稱,請編輯 [關聯性名稱] 文字方塊的內容。To change this name, edit the contents of the Relationship Name text box.

  8. 選擇 [確定] 建立關聯性。Choose OK to create the relationship.

在新的資料表建立外部索引鍵Create a foreign key in a new table

使用 Transact-SQLUsing Transact-SQL

下列範例會建立資料表並在 TempID 資料行上定義外部索引鍵條件約束,而此資料行會參考 AdventureWorks 資料庫 Sales.SalesReason 資料表中的 SalesReasonID 資料行。The following example creates a table and defines a foreign key constraint on the column TempID that references the column SalesReasonID in the Sales.SalesReason table in the AdventureWorks database. ON DELETE CASCADE 和 ON UPDATE CASCADE 子句用來確定對 Sales.SalesReason 資料表所做的變更會自動傳播至 Sales.TempSalesReason 資料表。The ON DELETE CASCADE and ON UPDATE CASCADE clauses are used to ensure that changes made to Sales.SalesReason table are automatically propagated to the Sales.TempSalesReason table.

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

在現有的資料表建立外部索引鍵Create a foreign key in an existing table

使用 Transact-SQLUsing Transact-SQL

下列範例會在 TempID 資料行上建立外部索引鍵,並參考 AdventureWorks 資料庫 Sales.SalesReason 資料表中的 SalesReasonID 資料行。The following example creates a foreign key on the column TempID and references the column SalesReasonID in the Sales.SalesReason table in the AdventureWorks database.

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

如需詳細資訊,請參閱:For more information, see: