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 .
 
Search WWH ::




Custom Search