SQL Server: creating a foreign key

Article
1/17/2024

In a forum post on MSDN someone asked how Microsoft SQL Server how a foreign key relationship needs to be implemented. In particular, it was the question of which locks Microsoft SQL Server sets and what impact these locks have on performance. I have been a little closer to the "Internals" and summarized it in this article.

Credits

Originally published at: http://www.db-berater.de/2018/03/inside-the-engine-erstellung-eines-fremdschlssels/

Foreign Key

Definition

Foreign keys are implemented in relational database systems to allow the referential integrity of records from different tables. For example, orders cannot be recorded in an order table if there is no matching customer. A foreign key constraint does not necessarily have to be linked to a PRIMARY KEY constraint in another table. It can be defined to point to the columns of a UNIQUE constraint in another table.

Implementation

A foreign key is a limitation; it affects the metadata of the table itself. Therefore, the implementation is always associated with an ALTER TABLE.

ALTER  TABLE dbo.CustomerOrders
ADD CONSTRAINT fk_Customers_Id
FOREIGN  KEY (Customer_Id)
REFERENCES dbo.Customers(Id);
GO

The code sample creates a foreign key constraint on the [dbo] table. [CustomerOrders] on the [dbo] table. [Customers] on the [Customer_Id] attributes on [Id]. This ensures that no orders can be captured if there is no matching [ID] in the [dbo]. [Customers] table.

Insight the engine

How does Microsoft SQL Server handle this request internally? There are two questions:

What resources are blocked while creating a foreign key constraint?
What steps need to be performed internally to implement a foreign key constraint?

Resources

To determine which resources are blocked during the implementation, we run the foreign key creation in a transaction. This is how the set locks can be examined.

BEGIN  TRANSACTION;
GO
 ALTER TABLE dbo.CustomerOrders
 ADD CONSTRAINT fk_Customers_Id
 FOREIGN KEY (Customer_Id)
 REFERENCES dbo.Customers(Id);
 GO 
 SELECT DTL.resource_type,
 DTL.request_mode,
 DTL.request_type,
 DTL.request_status,
 CASE resource_type
 WHEN N'OBJECT'  THEN OBJECT_NAME(resource_associated_entity_id)
 WHEN N'KEY'  THEN OBJECT_NAME(P.object_id)
 ELSE NULL
 END AS resource_object
 FROM  sys.dm_tran_locks AS DTL
 LEFT JOIN sys.partitions AS P
 ON DTL.resource_associated_entity_id = P.hobt_id
 WHERE  request_session_id = @@SPID;
 GO

Microsoft SQL Server sets [SCH-M] (Schema Modification) locks on both the affected tables and the foreign key object. Type [Sch-M] locks are used by Microsoft SQL Server when performing DDL operations on objects. During a [Sch-M] lock, concurrent access to the table is prevented.

The key locks [KEY] are used in the above context to store the schema changes in the internal system objects.

sys.sysschobjs	Each line represents an object in the database.
sys.syssingleobjrefs	Contains one line for each general N to 1 reference.

Workload

When implementing a foreign key constraint, we suspect a lot of work for Microsoft SQL Server; But this is not the case. The following figure shows the transaction steps that are performed during the implementation.

SELECT [Current LSN],
 Operation,
 Context,
 [Lock Information]
FROM  sys.fn_dblog(NULL, NULL)
ORDER BY
 [Current LSN] ASC;
GO

What looks cumbersome and complicated at a first glance turns out to be logical when one looks at the individual steps:

After the transaction has been opened (line 4), a schema modification lock is set to the [dbo]. [CustomerOrders] object.
Once the lock has been successfully set, an entry for the foreign key can be placed in the [sys]. [Sysschobjs] table. Only when the name of the foreign key object is entered, the internal ID for the object is known.
If the ID is known, the object can be protected with an SCH-M lock for outside access (line 7).
Subsequently, the foreign key restriction information will be entered in the system table (lines 8 - 11).

There are not several entries but the system table has several indices (4!). In the first step, the existing entry for [dbo]. [CustomerOrders] has been changed (status); then the foreign key object was added.
If all foreign key information is captured, the [dbo]. [Customers] table is blocked (line 12)
In lines 13-19, data is again written to the system tables [sys]. [Syssingleobjrefs].
After all the information about the foreign key relationship has been manifested, the locks can be unlocked (lines 20-25).
before the transaction is terminated (line 27)

Summary

The implementation of a foreign key is treated by Microsoft SQL Server very efficiently. The most important prerequisite for the quick implementation of a foreign key is the setting of a SCH-M lock on the affected objects, then the rest is just a breeze.

Share via