Sql Server: Foreign Keys

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.

Limits and restrictions

A foreign key constraint doesn"t have khổng lồ be linked only khổng lồ a primary key constraint in another table. Foreign keys can also be defined lớn reference the columns of a quality 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 lớn 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 another column in the same table, & is referred khổng lồ as a self-reference.

A FOREIGN KEY constraint specified at the column level can danh mục 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 cấp độ 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 of FOREIGN KEY constraints owned by other tables that reference a specific table. However, the actual number of FOREIGN KEY constraints used is limited by the hardware configuration, và by the design of the database & application. A table can reference a maximum of 253 other tables và columns as foreign keys (outgoing references). SQL Server năm 2016 (13.x) và later increases the limit for the number of other tables and columns that can reference columns in a single table (incoming references), from 253 lớn 10,000. (Requires at least 130 compatibility level.) The increase has the following restrictions:

Greater than 253 foreign key references are supported for DELETE và UPDATE DML operations. MERGE operations aren"t supported.A table with a foreign key reference khổng lồ itself is still limited to lớn 253 foreign key references.Greater than 253 foreign key references aren"t currently available for columnstore indexes, memory-optimized tables, or Stretch Database.

Important

Stretch Database is deprecated in SQL server 2022 (16.x). This feature will be removed in a future version of kftvietnam.com SQL Server. Avoid using this feature in new development work, và plan khổng lồ modify applications that currently use this feature.

FOREIGN KEY constraints aren"t enforced on temporary tables.

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

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

Xem thêm: Canh Cá Quả Nấu Rau Cần Đậm Đà, Hấp Dẫn, Canh Cá Lóc Rau Cần

In the Foreign-key Relationships dialog box, select Add.

The relationship appears in the Selected Relationship các mục with a system-provided name in the format FK__, 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 simply a default và common naming convention for the (Name) field of the foreign key object.

Select the relationship in the Selected Relationship list.

Select Tables & Columns Specification in the grid lớn the right và select the ellipses (...) lớn the right of the property.

In the Tables và Columns dialog box, in the Primary Key drop-down list, choose the table that will be on the primary-key side of the relationship.

In the grid beneath, choose the columns contributing lớn the table"s primary key. In the adjacent grid cell khổng lồ the right of each column, choose the corresponding foreign-key column of the foreign-key table.

Table Designer suggests a name for the relationship. Khổng lồ change this name, edit the contents of the Relationship Name text box.

Choose OK to lớn create the relationship.

Close the table designer window và Save your changes for the foreign key relationship change khổng lồ take effect.

Create a foreign key in a new table

Use Transact-SQL

The following example creates a table & 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 và ON UPDATE CASCADE clauses are used to ensure that changes made to lớn Sales.SalesReason table are automatically propagated to lớn 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 & references the column SalesReasonID in the Sales.SalesReason table in the AdventureWorks database.

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