Foreign Key References Check
Introduction
The Foreign Key References Check operator is used to verify foreign key relations after a delete or update operation. It is normally only used for tables that are referenced by more than 253 foreign keys, although it is possible to force this operator in other cases.
When rows are deleted from a table that is referenced by a foreign key, the optimizer must verify that the referencing table has no rows that reference the removed rows. The same applies for the old values in updated key columns. For these cases, the optimizer normally uses a probed semi join to the referencing table, plus an Assert operator to rollback the transaction and force an error if a row is found.
When the target of an update or delete statement is referenced by lots of foreign keys, this can result in long compilation times and very large execution plans. To alleviate that pain, the Foreign Key References Check was introduced in SQL Server 2016, when the product specifications were changed to allow more than 253 foreign key relations to a single table.
A single Foreign Key References Check operator can verify one or more foreign keys that target a single primary key or unique constraint (candidate key) in the referenced table. For foreign keys that target a different candidate key in the same table, separate Foreign Key References Check operators are used. As far as known, the optimizer will never create an execution plan where some foreign keys are verified with a Foreign Key References Check operator, and others with the classic pattern of semi join and Assert.
The Foreign Key References Check operator is only used for disk-based rowstore tables. If a table has a clustered columnstore index, or if a table is memory-optimized, then the classic pattern with lots of probed semi joins and an Assert is always used, regardless of the number of foreign keys to verify.
The Foreign Key References Check is also not used in execution plans for MERGE statements with more than one WHEN clause, even when they delete rows or update key columns. This was documented as a limitation for the first version on release of this feature, but the limitation is still there in SQL Server 2025.
Finally, there is no similar alternative for inserts (and new values of updated key columns) in a table that has more than 253 foreign keys to one or more other tables.
Visual appearance in execution plans
Depending on the tool being used, a Foreign Key References Check operator is displayed in a graphical execution plan as shown below:
|
SSMS and ADS |
Legacy SSMS |
Plan Explorer |
Paste The Plan |
 |
(not supported) |
 |
(no icon) |
Algorithm
The basic algorithm of the Foreign Key References Check operator is as shown below:
Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a row is returned, and resumes where it was upon next being called.
Action Column?
The input to the Foreign Key References Check operator may contain an “action column”, to indicate the type of change for the row passed in. If so, then the operator skips inserts, and checks updates for the old values of affected columns.
The known values and their meaning for the action column (for any operator, not just for Index Update) are:
- 1: Update
- 3: Delete
- 4: Insert
It is currently unknown whether there are more possible values.
If there is no action column in the input, then all rows are processed.
Search Matches
For each foreign key relationship to check, the optimizer decides the index to use and the index access pattern. This can result in three possible access strategies. In order of preference, they are:
- “Full matching index”: Used when there is an index that has all the Foreign Key columns as its leading columns.
For each row in the input, an Index Seek is performed into the referencing table, to find the first row that matches the values in the referenced columns. If any matching row is found, the operator forces an error.
In this case, the ForeignKeyReferencesCheck property includes an IndexScan (!) subproperty with the matching index specified, and the Ordered subproperty set to True. There is no SeekPredicates I suspect that this is an omission in the conversion of the execution plan from its internal format to XML. - “Partial matching index”: Used when the leading columns of the clustered index are a subset of the foreign key columns. So for instance, the foreign key is on columns Col1, Col2, and the clustered index is on Col1 only, or Col1 followed by any column other than Col2.
For each row in the input, an Index Seek is performed into the referencing table, to read all rows that match the values in that subset of columns. Each row is then checked to see whether the other referenced columns have matching values as well. If any matching row is found, the operator forces an error.
In this case, the ForeignKeyReferencesCheck property includes an IndexScan (!) subproperty with the clustered index specified, and the Ordered subproperty set to True, and the Predicate property set to the condition for the columns that remain after the subset that is used for the Index Seek. There is no SeekPredicates I suspect that this is an omission in the conversion of the execution plan from its internal format to XML.
In theory, this strategy could also be used with a covering nonclustered index that has a subset of the foreign key columns as its leading columns. However, in practice, I have never seen the optimizer pick the partial matching index strategy for a nonclustered index. - “No matching index”: Picked when the matching index and partial matching index strategies are not available.
For each row in the input, a full Clustered Index Scan (or Table Scan for a heap table) is done, to search for rows where the referenced columns match the current values. If any matching row is found, the operator forces an error.
In this case, the ForeignKeyReferencesCheck property includes an IndexScan subproperty with either the clustered index or the heap table specified, the Ordered subproperty set to False, and the Predicate property set to the condition that must be checked for each row.
Force error condition
As soon as any row is found in the Search Matches action, execution of the operator and the entire execution plan is halted, the transaction is rolled back, and an error message is returned to the client. The operator will not continue to search for other violations.
The error message will in this case specify the name of the foreign key constraint for which this violation was detected. However, neither the names, nor the object_ids of the constraints checked are included in the execution plan XML. I assume that this information is available in the actual internal representation of the execution plan, but is not included in the conversation to XML when an execution plan is presented.
More checks?
A Foreign Key References Check operator typically has 254 or more ForeignKeyReferenceCheck properties. Since SQL Server allows up to 10,000 foreign key references to a single table, I assume that that is also the maximum number of ForeignKeyReferenceCheck properties.
For each row the operator reads, each of the ForeignKeyReferenceCheck checks is verified in turn.
Performance consideration
The traditional foreign key verification pattern, that was always used on SQL Server 2014 and before, and is still used (unless hinted otherwise) when there are 253 or less foreign keys, uses a series of probed semi joins to the referencing tables (one for each foreign key), and then an Assert operator to force an error when any rows were found. This gives the optimizer several options. If there is no perfect index to seek, it can still choose to scan a covering index rather than the clustered index or a heap. Each join can be implemented using either Nested Loops or Merge Join. A Nested Loops might be further optimized by adding an Index Spool on the lower input. A Merge Join might necessitate a Sort, if no suitable index exists.
Those options are not available with the Foreign Key References Check operator. This operator basically always works similar to the Nested Loops algorithm, without its optimizations, and it can only seek an index that has the right leading columns, or scan the clustered index or heap. This hurts when many rows need to be verified, especially when the seek strategy is not available, as this scan will be repeated for each row removed from the referenced table.
The choice to use Foreign Key References Check or a series of probed semi joins and Assert is not made as a cost-based decision, but (unless hinted otherwise) purely based on the number of foreign keys to check. For up to 253 foreign keys (or when a MERGE with more than one THEN clause is used), the optimizer will always use a series of probed semi joins. For 254 or more foreign keys, it’s always the Foreign Key References Check operator.
Operator properties
The properties below are specific to the Foreign Key References Check operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Foreign Key References Check operator are marked with a *.
| Property name | Description |
|---|---|
| Actual I/O Statistics * | The Foreign Key References Check operator does not track its IO in this property. The only way to assess how much I/O the operator requires is to use the SET STATISTICS IO ON statement. |
| Estimated CPU Cost * | In my tests, this property was always zero for the Foreign Key References Check. Since the decision to use this operator or the standard pattern is made based on only the number of referencing foreign key constraints and not cost-based, Microsoft apparently decided not to track the estimated cost for this operator. |
| Estimated I/O Cost * | In my tests, this property was always zero for the Foreign Key References Check. Since the decision to use this operator or the standard pattern is made based on only the number of referencing foreign key constraints and not cost-based, Microsoft apparently decided not to track the estimated cost for this operator. |
| Estimated Operator Cost * | Since Estimated CPU Cost and Estimated I/O Cost are not computed for a Foreign Key References Check operator, this property (that is computed by Management Studio and other tools as the some of those two) is also always zero. |
| Foreign Key References Count | This property specifies the total number of foreign key constraints that this Foreign Key References Count operator checks. Note that the number of foreign keys that is checked by the full matching index access pattern is not explicitly represented in the execution plan, but can be computed by subtracting the No Matching Indexes Count and Partial Matching Indexes Count properties from the Foreign Key References Count. |
| ForeignKeyReferencesCheck | This property holds one or more IndexScan subproperties, one for each foreign key constraint to check (even if the actual access strategy is an Index Seek!). Each IndexScan further consists of the following subproperties:
(See also the main text) |
| No Matching Indexes Count | This property specifies the total number of foreign key constraints that this Foreign Key References Count operator checks by using the no matching index access pattern. |
| Partial Matching Indexes Count | This property specifies the total number of foreign key constraints that this Foreign Key References Count operator checks by using the partial matching index access pattern. |
Implicit properties
This table below lists the behavior of the implicit properties for the Foreign Key References Check operator.
| Property name | Description |
|---|---|
| Batch Mode enabled | The Foreign Key References Check operator supports row mode execution only. |
| Blocking | The Foreign Key References Check operator is non-blocking. |
| Memory requirement | The Foreign Key References Check operator does not have any special memory requirement. |
| Order-preserving | The Foreign Key References Check operator is fully order-preserving. |
| Parallelism aware | The Foreign Key References Check operator does not support parallelism. It can only be used in a serial plan, or in a serial section of a parallel plan. |
| Segment aware | The Foreign Key References Check operator is not segment aware. |
Change log
(Does not include minor changes, such as adding, removing, or changing hyperlinks, correcting typos, and rephrasing for clarity).
January 8, 2026: Added.