SQL SERVER – Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – T-SQL Example – Part 2 of 2

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)

About these ads

12 thoughts on “SQL SERVER – Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – T-SQL Example – Part 2 of 2

  1. 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)

    Like

  2. 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.

    Like

  3. nullif equivalent to case condition that is saying by msdn that link are:

    http://msdn.microsoft.com/en-us/library/ms177562.aspx

    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

    Like

  4. 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).

    Like

  5. Pingback: SQL SERVER – Not Possible – Delete From Multiple Table – Update Multiple Table in Single Statement « SQL Server Journey with SQL Authority

  6. 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.

    Like

  7. 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?

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #042 | Journey to SQL Authority with Pinal Dave

  9. 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.

    Like

  10. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s