Create foreign key relationships
Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
This article describes how to create foreign key relationships in SQL Server 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.
Permissions
Creating a new table with a foreign key requires CREATE TABLE permission in the database, and ALTER SCHEMA permission on the schema in which the table is being created.
Creating a foreign key in an existing table requires ALTER TABLE permission on the table.
Limitations
A foreign key constraint doesn't have to be linked only to a primary key constraint in another table. Foreign keys 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 aFOREIGN 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, specifyNOT 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, and 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 doesn't have a predefined limit on the number of
FOREIGN KEY
constraints a table can contain that reference other tables. The Database Engine also doesn't limit the number ofFOREIGN KEY
constraints owned by other tables that reference a specific table. However, the actual number ofFOREIGN KEY
constraints used is limited by the hardware configuration, and by the design of the database and application. A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x) and later versions increase the limit for the number of other tables and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Requires at least 130 compatibility level.) The increase has the following restrictions:Greater than 253 foreign key references are supported for
DELETE
andUPDATE
DML operations.MERGE
operations aren't supported.A table with a foreign key reference to itself is still limited to 253 foreign key references.
Greater than 253 foreign key references aren't currently available for columnstore indexes, or memory-optimized tables.
FOREIGN KEY
constraints aren't 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).
Create a foreign key relationship in Table Designer
Use SQL Server Management Studio
In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and select Design.
The table opens in Create and update database tables.
From the Table Designer menu, select Relationships. (See the Table Designer menu in the header, or, right-click in the empty space of the table definition, then select Relationships....)
In the Foreign-key Relationships dialog box, select Add.
The relationship appears in the Selected Relationship list with a system-provided name in the format
FK_<tablename>_<tablename>
, where the first tablename is the name of the foreign key table, and the second tablename is the name of the primary key table. This is just a default and common naming convention for the (Name) field of the foreign key object.Select the relationship in the Selected Relationship list.
Select Tables and Columns Specification in the grid to the right and select the ellipses (...) to the right of the property.
In the Tables and Columns dialog box, in the Primary Key dropdown list, choose the table that will be on the primary-key side of the relationship.
In the grid beneath the dialog box, choose the columns contributing to the table's primary key. In the adjacent grid cell to the right 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.
Choose OK to create the relationship.
Close the table designer window and Save your changes for the foreign key relationship change to take effect.
Create a foreign key in a new table
Use Transact-SQL
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. 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
Use Transact-SQL
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;
Related content
Feedback
https://aka.ms/ContentUserFeedback.
Binnenkort: Gedurende 2024 worden GitHub Issues uitgefaseerd als het feedbackmechanisme voor inhoud. Dit wordt vervangen door een nieuw feedbacksysteem. Ga voor meer informatie naar:Feedback verzenden en bekijken voor