Database development-reference integrity-use the delete on Cascade option in the external key

We need to pay attention to the integrity of references when designing databases. During my career as a DBA, I saw many designs taking an extreme path.

Before entering the detailed description of the delete cascade option, let's take a look at another option. You can set the udpate cascade option in a table with a foreign key. In my career, I have never met the need to update one or more columns with foreign keys.

Create an instance table:

In this example, create two tables and associate them with foreign keys. The master table has 99999 rows of records, and the child table has 19 records for each parent record. The creation statement is as follows:

-- Table creation Logic -- Parent table Create Table [DBO].[Order](  [Orderid] [Bigint] Not Null,  [Orderdata] [Varchar](10) Not Null,  Constraint [Pk_order_1] Primary Key Clustered ([Orderid] ASC) ) Go -- Child table Create Table [DBO].[Orderdetail](  [Orderdetailid] [Bigint] Not Null,  [Orderid] [Bigint] Null,  [Orderdata] [Varchar](10) Null,  Constraint [Pk_orderdetail] Primary Key Clustered ([Orderdetailid] ASC) ) Go -- Foreign key constraint Alter Table [DBO].[Orderdetail]With Check Add Constraint [Fk_orderdetail_order] Foreign Key([Orderid]) References [DBO].[Order]([Orderid]) On Delete Cascade Go -- Data load Declare @ Val Bigint Declare @ Val2 Bigint Select @ Val=1 While @ Val <100000 Begin  Insert Into DBO.[Order] Values(@ Val,'Test' + Cast(@ Val As Varchar)) Select @ Val2=1 While @ Val2 <20 Begin  insert into DBO . [orderdetail] values ( @ Val * 100000 ) + @ val2 , @ Val , 'test' + cast ( @ Val as varchar )) Select @ Val2=@ Val2+1 End Select @ Val=@ Val+1 End Go

Example 1:

Now let's remove a piece of data from the [order] Table. Note that I use DBCC dropcleanbuffers in each query to ensure that there is no data in the cache:

DBCC Dropcleanbuffers Go Delete From [Order] Where Orderid=24433 Go

After running the preceding statement, you can query the [orderdetail] Table to check whether the record has been removed. This is to understand what operations are required when the delete cascade option is not used to ensure that data is removed and their results are displayed:

Select * From Orderdetail Where Orderid=24433

After execution, no data is found. Run the following statement:

Alter Table [DBO].[Orderdetail] Drop Constraint [Fk_orderdetail_order] Go Alter Table [DBO].[Orderdetail]With Check Add Constraint [Fk_orderdetail_order] Foreign Key([Orderid]) References [DBO].[Order]([Orderid]) Go

Now let's run the script. Remember that when the deletecascade option is available, we must first start with [orderdetail]. When five or six tables have a foreign key association with a parent table, each table will be deleted separately before deleting the parent table.

DBCC Dropcleanbuffers Go Delete From [Orderdetail] Where Orderid=24032 Delete From [Order] Where Orderid=24032 Go

We can use SQL profiler to monitor the performance of the two processing methods. You can see that the delete cascade option consumes less resources:

Delete Cascade	CPU (MS)	Reads	Writes	Duration
Yes	281	12323	2	950
No	374	24909	3	1162

 

Example 2:

One of the best practices of sqlserver is to add indexes to the fields that frequently appear in the WHERE clause and join tables for foreign key columns. Now we add indexes to the [orderdetail] table and then run the preceding query, first, add the index:

Create Nonclustered Index Ix_orderdetail_orderid On DBO.[Orderdetail](Orderid) Go

Next, run the modified execution and monitor the performance:

DBCC Dropcleanbuffers Go Delete From [Orderdetail] Where Orderid=90032 Delete From [Order] Where Orderid=90032 Go Alter Table [DBO].[Orderdetail]With Check Add Constraint [Fk_orderdetail_order] Foreign Key([Orderid]) References [DBO].[Order]([Orderid]) On Delete Cascade Go DBCC Dropcleanbuffers Go Delete From [Order] Where Orderid=90433 Go

The following results show that the deletecascade option can be used to delete multiple tables with better performance and automatically clear sub-table data:

Delete cascade	CPU (MS)	reads	writes	duration
Yes	0	300	7	79
NO	0	312	6	64