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 (https://blog.sqlauthority.com)
12 Comments. Leave new
Hi Pinal,
I am curious to know more about design problems. I recently saw an article on slowly changing dimernsion but could not figure out how to use it in context with foreign key relationship. It would be great if you can add something to this.
In Workaround 3 Example 2 there are to variants I think:
1. Do not use ON DELETE CASCADE – Foreign key without Delete-Cascade means no delete on Products Table. You can bet on the following error: “Help, we can not delete rows in the Product-Table.”
2. Do not use a foreign key – Means we can delete in the products table without deleting the corresponding entries in the ProductDetails (Orphaned Data)
I like those bad advices actually. Rather they are the ones which went through my mind, when i thought for a workaround !!!
Hi Pinal,
I would like to go with Workaround 2: Design Changes – Column IsVisible
As in future if user wants to add particular product in catalogue just changing the flag value, user will be able to add in product catalogue.
nullif equivalent to case condition that is saying by msdn that link are:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql?view=sql-server-2017
what is wrong with followning case condition
(there is no data and type comptability related content in case condition so why do i get error in case condition)
————————————————
declare @expression1 varchar(33)
set @expression1=’2d’
declare @expression2 int
set @expression2=2
declare @a varchar(222)
set @a=CASE
WHEN (@expression1 IS NOT NULL) THEN @expression1
ELSE
@expression2
END
select @a
—————————————————————————-
please provide me exact and absolute answer
Good article. My solution would be to create a master products table, as you suggest in workaround 1, which includes an IsVisible column. Then I would create a view, with the same name as the original table, which selected all the visible products. This way the application code would not need to change – it would just be selecting from the view instead of the table. Finally write “instead of” triggers for insert/update/delete on the view to make the appropriate changes to the master table (e.g. instead of delete, set IsVisible to false in master table).
Systems of record usually don’t allow hard deletes because of referential integrity problems like the one outlined here. A good solution is to create an IsActive column on the Products table. A “delete” of a product would set this flag to False, which would be tantamount to saying that the product will no longer be offered for sale in the future. Queries for new order screens would constrain on IsActive = True. Inquiry screens looking up old orders would not constrain on the column, allowing the “deleted” products to still be displayed – this is obviously the right answer; just because a company no longer offers a product for sale does not mean that all sales records for that product should be expunged from the system.
Hi Pinal;
I also have a case of disappearing data. Some guys came over the weekend and worked in our main system which uses a SQL Server 2008 back-end. They actually printed some reports from the application of the work that they had done. However, on checking in the system today, there is no record that they did anything. I have also checked the database and there isn’t anything. I have so much confidence in the application as it is a well known ERP system that we have been using since 2003. Can SQL Server be the culprit here?
HI pinal,
how to alter a FK with delete cascade and update cascade.already FK created with delete cascade. i want to know how to add update cascade to existing FK.
Hi Pinal
I tried this example with Temp tables.Does Cascase delete works on temp tables. It dint work.
Thank you in advance.
Kind Regards
Shaik Mussarath
How do i delete Record from Multiple table in one Query Without Cascade?
trigger?