Why does updating a non-key column in the Parent Table take a lock on the Child Table

Given the following database with two tables having foreign key relationship between them: CREATE DATABASE FKLocksTest GO ALTER DATABASE FKLocksTest SET READ_COMMITTED_SNAPSHOT OFF USE FkLocksTest GO CREATE TABLE dbo.Department ( DeptID INT PRIMARY KEY, Deptname NVARCHAR(10) ) INSERT INTO dbo.Department VALUES (1,'IT'),(2,'HR') CREATE TABLE dbo.Person ( PersonID INT PRIMARY KEY, PersonName NVARCHAR(10), DepartmentId INT FOREIGN KEY REFERENCES dbo.Department(DeptId) ) INSERT INTO dbo.Person VALUES (1,'JohnSmith',2) If I run some updates, I can verify which locks are taken with traceflag 1200. Firstly, we can verify the object ids: SELECT OBJECT_ID('dbo.Department') AS Department, OBJECT_ID('dbo.Person') AS Person Now I enable TF1200 and run some updates, I can see the locks taken in the messages tab. I'll also ensure I am using read committed: DBCC TRACEON(1200,-1) SET TRANSACTION ISOLATION LEVEL READ COMMITTED and run my first update UPDATE dbo.Person SET PersonName = 'Jim' The Plan makes no reference to Department and the Traceflag output makes no reference to object id 581577110 which makes sense Next up, if I run UPDATE dbo.Person SET DepartmentId = 1 WHERE PersonID = 1 In this case,the execution plan references the department table and the Traceflag output shows locks taken on 581577110. This also makes sense as SQL Server has to check that the DepartmentId we are setting exists in the Department table This time, if I update the parent table and update the Primary key column UPDATE dbo.Department SET DeptID = 10 WHERE DeptID = 1 The execution plan shows a reference to the Person table and I can see locks taken on 613577224. This makes sense as SQL Server will need to check that there are no Person records with the old DepartmentID that we are updating. I also get an error as there is a Person who is associated with DeptId 1 Finally, if I run UPDATE dbo.Department SET Deptname = 'aaa' WHERE DeptID = 1 I can see in the Traceflag output that a lock is taken on the person table (613577224) but there is no reference to the person table in the plan The abridged output of TF1200 shows the locks taken on the Person table ...... Process 59 acquiring Sch-S lock on OBJECT: 9:613577224:0 (class bit0 ref1) result: OK ...... Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 ...... Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring X lock on OBJECT: 9:613577224:0 [UPDSTATS] (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 releasing lock on OBJECT: 9:613577224:0 [UPDSTATS] Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 ...... Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 ...... Process 59 releasing lock on OBJECT: 9:613577224:0 ...... Process 59 acquiring IS lock on OBJECT: 9:613577224:0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 ...... Msg 547, Level 16, State 0, Line 1 The UPDATE statement conflicted with the REFERENCE constraint "FK__Person__Departme__267ABA7A". The conflict occurred in database "FKLocksTest", table "dbo.Person", column 'DepartmentId'. The statement has been terminated. The lock on the Person table possibly appears to be part of the compilation process, if I run the UPDATE multiple times, I eventually get a much shorter list of locks (below in full) although the plan is a trivial plan Process 59 acquiring IX lock on OBJECT: 9:581577110:0 (class bit2000000 ref1) result: OK Process 59 acquiring IX lock on PAGE: 9:1:280 (class bit2000000 ref1) result: OK Process 59 acquiring X lock on KEY: 9:72057594043170816 (8194443284a0) (class bit2000000 ref1) result: OK Process 59 releasing lock reference on KEY: 9:72057594043170816 (8194443284a0) Process 59 releasing lock reference on PAGE: 9:1:280 My questions are, when we update a non-key column in the Department table: - Why is the lock on the Person table needed - Why is the lock only taken at compilation time