Let's add an index to the [Order Detail] table and run through the same scenario as in the example above.
Here is the DDL code for the index: DBCC DROPCLEANBUFFERS GO DELETE FROM [Order Detail] WHERE Order ID=90032 DELETE FROM [Order] WHERE Order ID=90032 GO ALTER TABLE [dbo].[Order Detail] WITH CHECK ADD CONSTRAINT [FK_Order Detail_Order] FOREIGN KEY([Order ID]) REFERENCES [dbo].[Order] ([Order ID]) ON DELETE CASCADE GO DBCC DROPCLEANBUFFERS GO DELETE FROM [Order] WHERE Order ID=90433 GO Looking at the results below we can see that with proper indexing we see almost no performance difference between the two methods.
While the later certainly can be a little more difficult to work with it ensures the integrity of your data stays intact.
The other end provides much more flexibility when it comes to updating and deleting data from your database, whether it's being done through the application or directly on the backend, but has the issue of possible orphan records if things are not done properly.
In the next couple of sections we'll outline what are the important questions to consider when choosing one approach over another.