Database development-Referential integrity-using the DELETE on CASCADE option in foreign keys

Original: Database Development-referential integrity-using the DELETE on CASCADE option in foreign keys

Original:

Http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/?utm_source= dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012731

Referential integrity needs to be taken into account when designing databases, and in my career as a DBA, I've seen a lot of design go the extreme way.

Before entering the detailed description of the DELETE CASCADE option, take a look at another option to set the udpate cascade option in a table with a foreign key. In my career, I have never encountered the need to update one or more columns through a foreign key.

To create an instance table:

In this example, you create two tables and associate them with foreign keys. The main table has 99999 rows of records, and the child table has 19 records for each parent record. Here is the CREATE statement:

--Table creation logic --parent table CREATE TABLE [dbo] . [Order] ( [OrderID] [bigint] not NULL, [OrderData] [varchar] (ten) 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] (ten) 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(@valas VARCHAR)) SELECT @val2 =1 while @val2 < 20 BEGIN       insert into dbo [orderdetail] values @val * 100000 Span style= "Color:gray" >) + @val2 @val ' TEST ' + cast ( @val as varchar SELECT @val2 = @val2 +1 END SELECT @val = @val +1 END GO

A first example:

Now let's remove a piece of data from the [Order] table, and note that I use DBCC dropcleanbuffers in each query to make sure there is no data in the cache:

DBCC dropcleanbuffers GO DELETE from [Order] WHERE OrderID =24433 GO

After you run the above statement, you can query the [OrderDetail] table to confirm that the record has been removed. This is to understand what to do when we do not use the DELETE CASCADE option, to ensure that the data is removed and to see their results:

SELECT * from OrderDetail WHERE OrderID =24433

There is no data to be found after execution. The following statement is executed:

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 and remember that when we have the Deletecascade option, we have to start with [OrderDetail]. To delete a record, imagine that when we have 5 or 6 tables with a foreign key association for a parent table, deleting the data will delete the parent table only after each table is deleted.

DBCC dropcleanbuffers GO DELETE from [OrderDetail] WHERE OrderID =24032 DELETE from [Order] WHERE OrderID =24032 GO

We can monitor the performance of two processing methods through SQL Profiler. You can see that the processing of the DELETE Cascade option consumes less resources:

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

A second example:

One of the SQL Server Best practices is the foreign key column and often the fields that appear in the WHERE clause, join table, plus the index, now we index the [OrderDetail] table, and then run the above query, first indexed:

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

As you can see from the results below, the use of the Deletecascade option is better for multiple table deletions and can automatically clear the child table data:

DELETE CASCADE	CPU (MS)	Reads	Writes	Duration
Yes	0	300	7	79
No	0	312	6	64

Database Development-referential integrity-using the DELETE on CASCADE option in foreign keys