| Author |
Topic |
|
Orbison
Starting Member
14 Posts |
 Posted - 2010-02-02 : 09:25:53
|
| 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 CASCADEGOALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Description]GOALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Detail] FOREIGN KEY([Detail_ID])REFERENCES [dbo].[Detail] ([Detail_ID])ON DELETE CASCADEGOALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Detail]GOMy 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
52326 Posts |
Posted - 2010-02-02 : 09:30:58
|
| 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 |
 |
|
|
Orbison
Starting Member
14 Posts |
Posted - 2010-02-02 : 10:00:43
|
| visakh16, thanks for your response! Can this be done by a join through the Product Table? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-02 : 10:04:25
|
| yup. it can be done |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-02 : 10:13:19
|
| I don't allow cascading deletes in my databases. |
|
|
|
MrQuizzles
Starting Member
20 Posts |
Posted - 2010-02-02 : 15:53:28
|
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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-03 : 03:26:24
|
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 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-03 : 04:17:16
|
| "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!! |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-03 : 06:43:37
|
| 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.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-03 : 07:15:39
|
| 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. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-03 : 07:20:03
|
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 leakRecords can and are ALWAYS accessed by "normal" meansIt 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. |
|
|
|
MrQuizzles
Starting Member
20 Posts |
Posted - 2010-02-03 : 16:54:20
|
| 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. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-03 : 18:32:28
|
| You don't understand. If you have a foreign key you CAN'T orphan data. The delete fails. |
|
|
|
MrQuizzles
Starting Member
20 Posts |
Posted - 2010-02-05 : 12:40:17
|
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. |
|
|
|
|
Cascading Deletes in SQL Server 2008