Database Reference
In-Depth Information
Listing 7-5.
Replacing the constraint with ON DELETE CASCADE action
alter table dbo.OrderLineItems drop constraint FK_OrderLineItems_Orders;
alter table dbo.OrderLineItems
with check
add constraint FK_OrderLineItems_Orders
foreign key(OrderId)
references dbo.Orders(OrderId)
on delete cascade;
Figure 7-5.
Deleting a row from the referenced table (ON DELETE CASCADE action)
There is one very important thing to remember: when you create the foreign key constraint, SQL Server requires
you to have a unique index on the
referenced
(
OrderId
) column in the
referenced
(
Orders
) table. However, there is no
requirement to have a similar index on the
referencing
(
OrderLineItems
) table. If you do not have such an index, any
referential integrity checks on the referencing tables will introduce the scan operation. In order to prove this, let's drop
the clustered index on the
OrderLineItems
table, as shown in Listing 7-6.
Listing 7-6.
Dropping the clustered index from the OrderLineItems table
drop index IDX_OrderLineItems_OrderId_OrderLineItemId on dbo.OrderLineItems
Now when you run the deletion again, you will see the execution plan, as shown in Figure
7-6
.
