SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cascading Deletes in SQL Server 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Orbison
Starting Member

14 Posts

Posted - 02/02/2010 :  09:25:53  Show Profile  Reply with Quote
Hi Guys,
I have 3 Tables 1) Product 2) Description and 3) Detail. I would like my delete to cascade to 2 other Tables when an entry gets deleted on my Description Table.

I've defined the following constraints on my Product Table:

ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Description] FOREIGN KEY([Product_Desc_ID])
REFERENCES [dbo].[Description] ([Product_Desc_ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Description]
GO

ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Detail] FOREIGN KEY([Detail_ID])
REFERENCES [dbo].[Detail] ([Detail_ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Detail]
GO

My Tables are defined as:

1) Product
Product_ID(PK),
Product_Desc_ID,
Detail_ID,
...

2) Description
Product_Desc_ID (PK),
Description,
...

3) Details
Detail_ID (PK),
Price,
...

When i delete an entry from my Description Table it only deletes the associated entries from the Product Table and not the Detail Table. Should my second constraint look after this or do i need to do an InnerJoin on the Detail Table to delete the entry?

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/02/2010 :  09:30:58  Show Profile  Reply with Quote
you dont have any referential constraint from Detail to refer to Description. Only then can you expect automatic deletion of record in Detail when Description is deleted
Go to Top of Page

Orbison
Starting Member

14 Posts

Posted - 02/02/2010 :  10:00:43  Show Profile  Reply with Quote
visakh16, thanks for your response! Can this be done by a join through the Product Table?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/02/2010 :  10:04:25  Show Profile  Reply with Quote
yup. it can be done
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 02/02/2010 :  10:13:19  Show Profile  Visit russell's Homepage  Reply with Quote
I don't allow cascading deletes in my databases.
Go to Top of Page

MrQuizzles
Starting Member

20 Posts

Posted - 02/02/2010 :  15:53:28  Show Profile  Reply with Quote
quote:
Originally posted by russell

I don't allow cascading deletes in my databases.



I'd be interested in knowing why not. The absence of cascading deletes could lead to what is essentially a memory leak (just with records instead of memory addresses; same idea, though). You end up with records that can't be accessed through normal means. They'll just sit there, taking up space until you manually come along and delete them.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/03/2010 :  03:26:24  Show Profile  Reply with Quote
quote:
Originally posted by MrQuizzles

quote:
Originally posted by russell

I don't allow cascading deletes in my databases.



I'd be interested in knowing why not. The absence of cascading deletes could lead to what is essentially a memory leak (just with records instead of memory addresses; same idea, though). You end up with records that can't be accessed through normal means. They'll just sit there, taking up space until you manually come along and delete them.


yup. orphaned records can occur. using cascading deletes make sure tidying up happens automatically
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/03/2010 :  04:17:16  Show Profile  Reply with Quote
"I'd be interested in knowing why not. The absence of cascading deletes could lead to what is essentially a memory leak (just with records instead of memory addresses; same idea, though). You end up with records that can't be accessed through normal means. They'll just sit there, taking up space until you manually come along and delete them."

You can have the FKeys without the Cascade deletes, so there will be no orphans.

We don't allow cascade deletes either, we do it under program control. There may be a few cases where we will delete Child records and then the Parent record, without asking the user, but in most cases we require the user to delete the child records first - that ensures that they are aware of what they are doing!

Also, if some program logic wants to delete an Order then it must delete Order Items first. If a program just deletes an Order and the system silently deletes the Order items, that may hide a bigger problem - that the programmer did not know the two were related. (If programmer writes code to delete Order and gets a Referential Integrity Error "Can't delete Order; Order Items exist" then s/he thinks about whether to delete order items first OR NOT!!
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 02/03/2010 :  06:43:37  Show Profile  Reply with Quote
A lot of people would probably disagree but I think that the need for allowing cascade deletes is due to lazy programmers and poor application control. Foreign keys are there to make sure that referential integrity is intact but also to make sure you don't screw up...it's close to impossible to keep track of what will really happen when using cascading delete in a large system.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 02/03/2010 :  07:15:39  Show Profile  Visit russell's Homepage  Reply with Quote
Exactly what Kristen and Lumbago said.

Foregin Keys prevent orphaned records. Allowing them to cascade does nothing to prevent orphaned records or preserve data integrity, it is just lazy. It also often introduces difficult to track down bugs and inadvertant data loss.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 02/03/2010 :  07:20:03  Show Profile  Visit russell's Homepage  Reply with Quote
quote:
Originally posted by MrQuizzles

I'd be interested in knowing why not. The absence of cascading deletes could lead to what is essentially a memory leak (just with records instead of memory addresses; same idea, though). You end up with records that can't be accessed through normal means. They'll just sit there, taking up space until you manually come along and delete them.



quote:
Originally posted by visakh16

yup. orphaned records can occur. using cascading deletes make sure tidying up happens automatically



Not to single you guys out, but it is an important point that the above statements are incorrect.

It does not amount to a memory leak
Records can and are ALWAYS accessed by "normal" means
It does not create orphaned records.

What it does do, is deletes data that was not EXPLICITLY mentioned in a delete statement. To me, this is wholly unacceptable.
Go to Top of Page

MrQuizzles
Starting Member

20 Posts

Posted - 02/03/2010 :  16:54:20  Show Profile  Reply with Quote
Well, when I say "normal means" I mean through the application that's using the database. Certainly, if you can break the database through the application that uses it, you're doing it wrong. I'm a big fan of encapsulation, as well.

Finding orphans in a database is certainly much easier than finding leaks in main memory, but the idea's the same. I do suppose that the ease of finding orphans in a database makes implementing the equivalent of garbage collection much easier as well.


The relationships I'm working with right now are relatively simple, so I find cascading deletes to be convenient and easy to keep track of; a better way than hard-coding them into my application. It's just a bunch of one-to-many relationships where, if I delete the one, the many become useless orphans, data that describes nothing. Perhaps if I ever deal with more complex databases, I'll feel the way you do about them.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 02/03/2010 :  18:32:28  Show Profile  Visit russell's Homepage  Reply with Quote
You don't understand. If you have a foreign key you CAN'T orphan data. The delete fails.
Go to Top of Page

MrQuizzles
Starting Member

20 Posts

Posted - 02/05/2010 :  12:40:17  Show Profile  Reply with Quote
quote:
Originally posted by russell

You don't understand. If you have a foreign key you CAN'T orphan data. The delete fails.



Ah, ok. I've just been attaching cascading deletes to my foreign keys, so I've not run into that.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000