Databases Reference
In-Depth Information
Figure 6-2:
A delete example.
As you can see in this plan, in addition to
CountryRegion
, two additional tables
(
StateProvince
and
CountryRegionCurrency
) are accessed. The reason behind
this is that these two tables have a foreign key referencing
CountryRegion
, and so SQL
Server needs to validate that no records exist on these tables for this specific value of
CountryRegionCode
. So, the tables are accessed, and an Assert operator is included at
the end of the plan to perform this validation. If a record with the
CountryRegionCode
to be deleted exists in any of these tables, the Assert operator will throw an exception and
SQL Server will roll back the transaction, returning the following error message:
Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK_StateProvince_CountryRegion_
CountryRegionCode." The conflict occurred in database "AdventureWorks," table "Person.
StateProvince," column 'CountryRegionCode'.
Listing 6-3.
So as you can see, the previous example showed how update operations can access some
other tables not included in the original query, in this case, because of the definition of
referential integrity constraints. The updating of non-clustered indexes is covered in the
next section.
