Create Foreign Key Relationships

This topic describes how to create foreign key relationships in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. You create a relationship between two tables when you want to associate rows of one table with rows of another.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Security

• To Create Foreign Key Relationships by using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

• 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.

• 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. To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.

• FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER (Transact-SQL).

• FOREIGN KEY constraints can reference another column in the same table. This is referred to as a self-reference.

• 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.

• 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.

• The Database 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. 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. We recommend that a table contain no more than 253 FOREIGN KEY constraints, and that it be referenced by no more than 253 FOREIGN KEY constraints.

• FOREIGN KEY constraints are not enforced on temporary tables.

• If a foreign key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering. For more information, see CLR User-defined Types.

• 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).

Security

Permissions

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.

Creating a foreign key in an existing table requires ALTER permission on the table.

[Top]

Using SQL Server Management Studio

To create a foreign key relationship in Table Designer

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.

   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.

[Top]

Using Transact-SQL

To create a foreign key in a new table

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. The example creates a table and defines a foreign key constraint on the column TempID that references the column SalesReasonID in the Sales.SalesReason table. 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.

   USE AdventureWorks2012;
   GO
   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
   );GO
   

To create a foreign key in an existing table

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. The example creates a foreign key on the column TempID and references the column SalesReasonID in the Sales.SalesReason table.

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

   For more information, see ALTER TABLE (Transact-SQL), CREATE TABLE (Transact-SQL), and table_constraint (Transact-SQL).

[Top]