SQL SERVER – Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – T-SQL Example – Part 2 of 2
- by pinaldave
Yesterday I wrote a real world story of how a friend who thought they have an issue with intrusion or virus whereas the issue was really in the code. I strongly suggest you read my earlier blog post Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – Part 1 of 2 before continuing this blog post as this is second part of the first blog post.
Let me reproduce the simple scenario in T-SQL.
Building Sample Data
USE [TestDB]
GO
-- Creating Table Products
CREATE TABLE [dbo].[Products](
[ProductID] [int] NOT NULL,
[ProductDesc] [varchar](50) NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)) ON [PRIMARY]
GO
-- Creating Table ProductDetails
CREATE TABLE [dbo].[ProductDetails](
[ProductDetailID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Total] [int] NOT NULL,
CONSTRAINT [PK_ProductDetails] PRIMARY KEY CLUSTERED
(
[ProductDetailID] ASC
)) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductDetails] WITH CHECK ADD CONSTRAINT [FK_ProductDetails_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
-- Insert Data into Table
USE TestDB
GO
INSERT INTO Products (ProductID, ProductDesc)
SELECT 1, 'Bike'
UNION ALL
SELECT 2, 'Car'
UNION ALL
SELECT 3, 'Books'
GO
INSERT INTO ProductDetails
([ProductDetailID],[ProductID],[Total])
SELECT 1, 1, 200
UNION ALL
SELECT 2, 1, 100
UNION ALL
SELECT 3, 1, 111
UNION ALL
SELECT 4, 2, 200
UNION ALL
SELECT 5, 3, 100
UNION ALL
SELECT 6, 3, 100
UNION ALL
SELECT 7, 3, 200
GO
Select Data from Tables
-- Selecting Data
SELECT *
FROM Products
SELECT *
FROM ProductDetails
GO
Delete Data from Products Table
-- Deleting Data
DELETE
FROM Products
WHERE ProductID = 1
GO
Select Data from Tables Again
-- Selecting Data
SELECT *
FROM Products
SELECT *
FROM ProductDetails
GO
Clean up Data
-- Clean up
DROP TABLE ProductDetails
DROP TABLE Products
GO
My friend was confused as there was no delete was firing over ProductsDetails Table still there was a delete happening. The reason was because there is a foreign key created between Products and ProductsDetails Table with the keywords ON DELETE CASCADE. Due to ON DELETE CASCADE whenever is specified when the data from Table A is deleted and if it is referenced in another table using foreign key it will be deleted as well.
Workaround 1: Design Changes – 3 Tables
Change the design to have more than two tables. Create One Product Mater Table with all the products. It should historically store all the products list in it. No products should be ever removed from it. Add another table called Current Product and it should contain only the table which should be visible in the product catalogue. Another table should be called as ProductHistory table. There should be no use of CASCADE keyword among them.
Workaround 2: Design Changes - Column IsVisible
You can keep the same two tables. 1) Products and 2) ProductsDetails. Add a column with BIT datatype to it and name it as a IsVisible. Now change your application code to display the catalogue based on this column. There should be no need to delete anything.
Workaround 3: Bad Advices
(Bad advises begins here) The reason I have said bad advices because these are going to be bad advices for sure. You should make necessary design changes and not use poor workarounds which can damage the system and database integrity further. Here are the examples 1) Do not delete the data – well, this is not a real solution but can give time to implement design changes. 2) Do not have ON CASCADE DELETE – in this case, you will have entry in productsdetails which will have no corresponding product id and later on there will be lots of confusion. 3) Duplicate Data – you can have all the data of the product table move to the product details table and repeat them at each row. Now remove CASCADE code. This will let you delete the product table rows without any issue. There are so many things wrong this suggestion, that I will not even start here. (Bad advises ends here)
Well, did I miss anything? Please help me with your suggestions.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
