Database Reference
In-Depth Information
Now let's add the foreign key constraint to the table. Listing 7-4 shows the
ALTER TABLE
statement, which
performs this task.
Listing 7-4.
Adding a foreign key constraint to the OrderLineItems table
alter table dbo.OrderLineItems
with check
add constraint FK_OrderLineItems_Orders
foreign key(OrderId)
references dbo.Orders(OrderId)
When you run the insert again, you will see that execution plan changes, as shown in Figure
7-3
.
Figure 7-3.
Inserting a row into the referencing table with a foreign key constraint defined
As you see, the plan now includes a clustered index seek operation on the
referenced
(
Orders
) table. SQL Server
needs to validate the foreign key constraint and make sure that there is a corresponding order row for the line item
that you are inserting.
Now let's see what happens when you delete the row from the
Orders
table. As you see in Figure
7-4
, our
execution plan now includes a clustered index seek on the
referencing
(
OrderLineItems
) table. SQL Server needs to
check to see if there are any line item rows that reference the row you are deleting. If there are any such line item rows,
SQL Server either aborts the deletion or performs some cascade actions, depending on the rules of the foreign key
constraint.
Figure 7-4.
Deleting a row from the referenced table (no cascade actions)
Let's add
ON DELETE CASCADE
action to the foreign key constraint, as shown in Listing 7-5. Now when you delete
the row from the
Orders
table, SQL Server needs to find and delete the referencing rows from
OrderLineItems
table.
The execution plan is shown in Figure
7-5
.