Cascade Delete and N:N Join Tables

I'm revising the schema for an app I've just taken over and most of
the N:N tables don't have unique indexes defined on the pair of
foreign keys (so they have lots of duplicate records), but the FK
relationships are all set as CASCADE DELETE (and, unnecessarily,
CASCADE UPDATE, since all the FKs values are from Autonumber fields,
which can't be updated, of course). 

Anyway, my tendency is to *not* set CASCADE DELETE on join tables,
because I'm never quite sure if the way *I* see the hierarchy is the
way Jet sees it. That is, I may think that something is the parent,
but Jet sometimes sees it as the child (e.g., lookup tables seem
subordinate to the main table, but they are actually the parent
tables if you define relationships for them, which I very often
don't, as a matter of fact). 

Obviously, if one of the two parents of the N:N record is deleted,
then the join record is invalidated, but should I prevent that? 

Or is the answer "it depends"?

In the present case, I'm included to cascade the deletions because
in the tables I'm working with, it would be OK to lose the record. 

On the other hand, neither of the parents should be deleted, but if
they are, it would be easier to have the cleanup at the db engine
level. 

I haven't yet decided if I want to allow these parents to be deleted
if they have children. In the past, the app has certainly been used
in that way (and that has resulted in a lot of orphan records
because RI was not enforced), but I'm not certain it should be in
the future. 

Thoughts?

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
2/3/2010 3:31:23 AM
access 16762 articles. 3 followers. Follow

4 Replies
1088 Views

Similar Articles

[PageSpeed] 52

On 3 Feb 2010 03:31:23 GMT, "David W. Fenton"
<XXXusenet@dfenton.com.invalid> wrote:

Hi David,
I was following you until you said "the way I see it". There really is
only one way to see a 1:M relationship with cascade delete: if the
parent is deletes the children will be deleted as well.

Therefore, in an M:M, records in the junction table will be deleted if
a parent record (of either side) is deleted.

Whether you should allow this or not depends on the business rules.
There is no generic answer. Personally I use cascade delete very
sparingly; often I will force the user to first manually delete the
children before they can delete the parent. That way the user can see
the impact of the decision to delete the parent. Again, it depends on
the business case.

-Tom.



>I'm revising the schema for an app I've just taken over and most of
>the N:N tables don't have unique indexes defined on the pair of
>foreign keys (so they have lots of duplicate records), but the FK
>relationships are all set as CASCADE DELETE (and, unnecessarily,
>CASCADE UPDATE, since all the FKs values are from Autonumber fields,
>which can't be updated, of course). 
>
>Anyway, my tendency is to *not* set CASCADE DELETE on join tables,
>because I'm never quite sure if the way *I* see the hierarchy is the
>way Jet sees it. That is, I may think that something is the parent,
>but Jet sometimes sees it as the child (e.g., lookup tables seem
>subordinate to the main table, but they are actually the parent
>tables if you define relationships for them, which I very often
>don't, as a matter of fact). 
>
>Obviously, if one of the two parents of the N:N record is deleted,
>then the join record is invalidated, but should I prevent that? 
>
>Or is the answer "it depends"?
>
>In the present case, I'm included to cascade the deletions because
>in the tables I'm working with, it would be OK to lose the record. 
>
>On the other hand, neither of the parents should be deleted, but if
>they are, it would be easier to have the cleanup at the db engine
>level. 
>
>I haven't yet decided if I want to allow these parents to be deleted
>if they have children. In the past, the app has certainly been used
>in that way (and that has resulted in a lot of orphan records
>because RI was not enforced), but I'm not certain it should be in
>the future. 
>
>Thoughts?
0
Tom
2/3/2010 3:52:18 AM
On 3 Feb 2010 03:31:23 GMT, "David W. Fenton" <XXXusenet@dfenton.com.invalid>
wrote:

>Anyway, my tendency is to *not* set CASCADE DELETE on join tables,
>because I'm never quite sure if the way *I* see the hierarchy is the
>way Jet sees it. That is, I may think that something is the parent,
>but Jet sometimes sees it as the child (e.g., lookup tables seem
>subordinate to the main table, but they are actually the parent
>tables if you define relationships for them, which I very often
>don't, as a matter of fact). 

Erm?

A Lookup table to main table relationship is always a One to Many (each lookup
value can be in many maintable values). I'd NEVER use Cascade Deletes in a
lookup table; that would delete "real data" (possibly lots of it) if you
deleted a lookup value that you mistakenly thought was obsolete.

The cascade always goes from One to Many. Never the other way!

Whether the *significance* in the real world is subordinate or preeminent is
irrelevant (he said polysyllabically).
-- 

             John W. Vinson [MVP]
0
John
2/3/2010 4:47:16 AM
Tom van Stiphout <tom7744.no.spam@cox.net> wrote in
news:6dshm5lbk7mulc7ej3e7bpd3ohapr1b4tm@4ax.com: 

> On 3 Feb 2010 03:31:23 GMT, "David W. Fenton"
><XXXusenet@dfenton.com.invalid> wrote:
> 
> I was following you until you said "the way I see it". There
> really is only one way to see a 1:M relationship with cascade
> delete: if the parent is deletes the children will be deleted as
> well. 
> 
> Therefore, in an M:M, records in the junction table will be
> deleted if a parent record (of either side) is deleted.

I'm not sure why you thought I wasn't clear on that.

But I'm very careful about the parent/child hierarchy because of my
Jet replication experience, where it matters a great deal (certain
kinds of legal relationships, such as a self-join to a required
field where the default is to set the FK field to the record's own
PK, can never work in a replicated scenario because INSERTs are done
before UPDATES, so the record can't be inserted because it's FK
value does not yet exist in the PK index), and it's not always
intuitive the way things actually work. 

I once had a circular relationship that caused no problems most of
the time but occasionally ended up in a deadlock, and it was because
I was viewing the relationships upside-down in comparison to the way
the database engine saw them. The db engine was right, of course,
but it was still counterintuitive, and that's why I don't assume
that my assumptions about how things should work are correct. 

> Whether you should allow this or not depends on the business
> rules. 

I'm probably changing the business rules, as there's a lot of
orphaned data, mostly because the app was not built to deal with the
issue of having sets of data that applied only to each year (when a
new year arrived, they'd delete a bunch of child data that no longer
applied, thus invalidating the old data, which was left in the
database), but I'm also wary of implementing prevention of deletes
since the users may complain about being unable to delete records. 

I think I'm going to implement no CASCADE DELETE and then explain to
them that I've tightened the data rules and if they need to delete
parent records, then I'll have to build a UI to do it for them (and
I may or may not actually delete the data -- using a delete flag,
instead). 

> There is no generic answer. Personally I use cascade delete very
> sparingly; often I will force the user to first manually delete
> the children before they can delete the parent. That way the user
> can see the impact of the decision to delete the parent. Again, it
> depends on the business case.

I generally don't use CASCADE DELETES much, except on something like
an invoice, where if deleting the invoice header is allowed, the
invoice items have no meaning and should be deleted along with the
header. On the other hand, I never cascade deletes from Customer to
Invoice, for obvious reasons. 

In this case, it's not quite so dire if the top-level record is
deleted, and there might very well be good reasons to delete them.
I'll have to work with the app to determine whether or not I want to
allow them to delete the parent records or not, and if so, under
what circumstances. There's some child data that is not important,
and other child data that shouldn't be deleted, so I may cascade
some of the deletes and not others. 

In general, though, I agree with the default position of *NOT*
cascading deletes, and only doing so when it's pretty clearly
sensible. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
2/3/2010 11:38:24 PM
John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:eovhm5dueq6lo5mf4pcht6rtp7t69uln59@4ax.com: 

> On 3 Feb 2010 03:31:23 GMT, "David W. Fenton"
> <XXXusenet@dfenton.com.invalid> wrote:
> 
>>Anyway, my tendency is to *not* set CASCADE DELETE on join tables,
>>because I'm never quite sure if the way *I* see the hierarchy is
>>the way Jet sees it. That is, I may think that something is the
>>parent, but Jet sometimes sees it as the child (e.g., lookup
>>tables seem subordinate to the main table, but they are actually
>>the parent tables if you define relationships for them, which I
>>very often don't, as a matter of fact). 
> 
> Erm?
> 
> A Lookup table to main table relationship is always a One to Many
> (each lookup value can be in many maintable values). I'd NEVER use
> Cascade Deletes in a lookup table; 

Nor do I, and I didn't say I did, or intend to imply that anyone
should... 

> that would delete "real data" (possibly lots of it) if you
> deleted a lookup value that you mistakenly thought was obsolete.
> 
> The cascade always goes from One to Many. Never the other way!

I know this. But things are not always as clean and simple as they
may seem intuitively. That's something that working with replication
taught me, that there's more going on than one may think. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
2/3/2010 11:40:07 PM
Reply:

Similar Artilces:

Comparing and Deleting rows
I have two spreadsheets. One is like this: _Computer_|_Date_ The other is like this: Computer | Last Used[ The problem is that the two lists are different, but some computer exist in both. The first list is a complete list, the second one is subset of the first. How do I extract rows that are common into a separate spreadsheet -- Message posted from http://www.ExcelForum.com what do you mean by extract. from which file. do what after extraction. why 2 files instead of 1 file-2 sheets. >-----Original Message----- >I have two spreadsheets. > >One is like this: > >...

Deleting Inbox
Hallo Is there a way to delete just the files in the Inbox without having to delete the hole .pst. If possible offline; without opening outlook. No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Martin asked: | Hallo | | Is there a way to delete just the files in the Inbox | without having to delete the hole .pst. If possible | offline; without opening outlook. Martin <anonymous@disc...

Unabel to Delete Mail From Outlook 98 In- Box
Subject: Deleting Mail From Outlook 98 In-Box From: "Barry J. Koffman" <baryjkoffman@bpiplus.com> Sent: 8/16/2003 8:05:50 PM I am running Outlook 98 on XP Home Edition. It has run fine until August 7th 2003. I elected to do the updates form XP and after that date I have been unable to delete e-mails in my inbox. I have also not been able to sync by my PDA. I receivew an error message that I have an interface error and to restart Outlook - This does not solve the problem. I have tried to reset the dates to previous dates before the changes but I can not...

how do I delete Salesperson IDs?
We are re-numbering all our Salesperson IDs. I would like to delete the old ones to ensure they are not assigned to accounts out of habit. However, I get the message "Commissions exist for this salesperson. The salesperson record cannot be deleted. We don't care for the sales / commissions history on the salesperson cards. I set everything I could see to 0 (on the Salesperson Maintenance and History), but still get the msg (ran checklinks just in case). Anyone have some suggestions what fields I need to clear to allow me to delete a Rep ID? Thx a lot, Maria. MS sells a Profe...

Deleting Switchboards
I have created a form with controls to replace my Switchboard, but when I open the database it is still looking for the Switchboard. How do I get rid of this from wherever it is looking? On Tue, 12 Jan 2010 16:24:01 -0800, Bill wrote: > I have created a form with controls to replace my Switchboard, but when I > open the database it is still looking for the Switchboard. How do I get rid > of this from wherever it is looking? Access 2003 or older? Tools + Start Up Replace the Switchboard listed in the Display Form/Page dialog box with the name of the form you wish t...

Two table query not giving expected result
I have two tables, contacts and companies, which are related by the companyID. The value in the contacts tables is a result from the input form where for avery contact one can choose the company using a combobox. Now I would like to populate a list box using a query which as input uses either the name (first or last) form one table or the company name from the second table. (lets assume that in the contacts table a lot of names are the same and that in the company table every record is unique) I use the following query to limit the output to the selected values: SELECT Contacts.ContactID,Cont...

Characters /r/n%20%20 in blank tags?
Hi! I have a Webservice which returns an XMLDocument type. In viewing the raw output steam, I noticed that if a tag value is blank, the data stream actually looks like this: <MyTag>\r\n </MyTag>. A CR/LF followed by two spaces. I have a special client to recieve this stream generally, but when I do testing just with IE I note that the formating of such blank tag fields is abbreviated -- perhaps this is something that was purposely designed to make browser output look clearer. But my question is this -- can I suppress this behavior? Best Harry * Harry Whitehouse w...

Account Master tables
On the GP Account card, there is a checkbox to "Allow Account Entry". Does anyone know what SQL table that is stored in? The table name is dbo.GL00100, the particular column in question is GL00100.ACCTENTR Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "VGrinam" wrote: > On the GP Account card, there is a checkbox to "Allow Account Entry". Does > anyone know what SQL table that is stored in? &g...

deleted subject line
i have a user that sent an email to 40 different addresses, when she sent it, it had the subject line filled in, after it sent the email the subject line was empty. She is using outlook 2002 with Sp3 microsoft office 10 and we have a win 200 exchange with SP3. is there a limit in the number of "To" addresees that one email can support and would that have any bering on the subject line being entries being deleted? There is a limit on the number of addresses in the To: line - by default it's 5000 in Global Settings (and fwiw, as high as 64000 in default SMTP virtual ser...

Cascading combo boxes
I would like to set up a form containing a combo box with a list of mechanisms of injury. Some mechanisms have sub groups that I would like to have visible by means of linking them with a cascading combo box. Is it possible to have only some records from Cbo 1 link to Cbo 2? The examples I have seen to date show cascading data for all records in the first combo ie the city/state or postcode/suburb examples where every record has a sub record. Thanks for your help. -- Joe Joe, If I understand correctly, you initially show just the first combo (ex. cboInjury), and on ...

Outlook problems -- deleted my mail acct., etc.
I am having a few problems with Outlook 2003 ("w/business contact manager"). I have already looked through the online help but can't find any answers. 1) My Hotmail acct. mysteriously disappeared from my email accounts and was replaced by a "business contact database". I deleted that, but I don't know where my hotmail acct. is (it was originally imported from Outlook Express). Does anyone know why this happened? Having email accounts with all of their messages is very bad. 2) I open a new message and click the "To..." label. In the form that pops u...

Deleting Exchange
I've added a new Exchange server to my network, Exchange 2003, and I've migrated all the mailboxes. I'm not trying to uninstall Exchange 2000 off of the old server when I bring it down. When I try to uninstall it through add/remove programs, I get an error message telling me that I can't remove it because "One or more users currently use a mailbox store on this server. These users must be moved to a mailbox store on a different server or be mail disabled before uninstalling this server." These are the boxes that are left: SMTP System Attendant SystemMailbox...

what happened to the table controls
Just upgraded from Publisher 2000 to 2003 ... what happened to the control "buttons" (the small tabs on the outside) on tables that would allow you to select a row, column or the entire table? Place your cursor close to the row or column you want to highlight, an arrow will appear. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Rob Odum" <RobOdum@discussions.microsoft.com> wrote in message news:4387D516-05DE-4ECE-A158-EAC92CA31877@microsoft.com... > Just upgraded from Publisher 2000 to 2003 ... what h...

Dimension Update and Pivot table report
I have an Excel (03) Report file which uses Analysis Services (SP4) Cube. Works fine still using AS 2000 not 2005. When I make any changes to Dimension and process Dim. and Cube Excel file doesn't takes my Dim changes... I have to take (drag) the changed Dim. back to field list and bring it back to report.... then only it works.....problem is we have 100s of Excel Cube Reports. Error I get: The item Could not be found in OLAP Cube. Thank you - Ashok ...

Changing font in rich text table
I want to standardise the font of all data in a table's rich text field. If I use: Dim DB As Database Dim rst As Recordset Dim strSQL As String Set DB = CurrentDb() strSQL = "SELECT tblReportTermly.Aims FROM tblReportTermly;" Set rst = DB.OpenRecordset(strSQL) Do Until rst.EOF With rst .MoveFirst .Edit !Aims.FontName = "Calibri" !Aims.FontSize = "11" .Update .MoveNext End With Loop i get "Object doesn't support this property or method". Am I approaching this incorrectly? ...

Alternatives to MS Query for Pivot Tables & Cubes
I am using Excel 2003. I would like to know if there is an add-in or other (preferably free) tool out there that I could use to create OLAP cubes for Excel pivot tables that would have better functionality than MS Query. I find MS Query very difficult to use and almost impossible for creating calculated fields that can then be built into a cube. (Note: I thought I could just make calculated fields in the pivot table itself, but now I see that Excel will not let you do that when using an OLAP cube as a source.) Any suggestions would be greatly appreciated. ...

cascade copy?
Is there such a thing as a cascade copy? I know there's cascade delete where if you delete the main record, all related subrecords are deleted but what if I want to do the reverse. For example I have a quote that has subpackages related to it. If I want to copy the main quote, I want all related subrecords copied along. Or is this a programming thing? Thanks, Alan "Alan" <Alan@discussions.microsoft.com> wrote in message news:7A7551D8-B482-4911-9452-3FE801FED2D4@microsoft.com... > Is there such a thing as a cascade copy? I know there's cascade delete where ...

Delete a person from the drop down in the to field
Hello, How do I delete a name from my automatic drop down menu in the to field. If I send a mail to fx. microsoft@microsoft.com, the next time I write mic... in the to field, the drop down menu keeps the address in the list. How do I delete this name again. Best regards Kim use the arrow buttons to select it and hit Del -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Outlook Tips: http://www.outlook-tips.net/ http://www.poremsky.com - http://www.cdolive.com Expert Zone http://www.microsoft.com/windowsxp/expertzone Search for answers: http://groups.go...

on NoData, show value in table
My report recordsourse is based on qrystudent where i have 2 tables. 'duration' table has 2 fields [from] and [to]. on NoData event of the report, I want to give message like 'You have not logged in during ' [from] and [to] If NoData, how can I give message include fields value in 'duration' table? 'duration' table has only one record. Thanks Song Su wrote: >My report recordsourse is based on qrystudent where i have 2 tables. >'duration' table has 2 fields [from] and [to]. > >on NoData event of the report, I want to give message l...

Autocorrect entries creating and deleting
Word 2002 I have managed to delete Word's default autocorrect entries. How do I delete multiple autocorrect entries that are under the replace heading? Also, I am trying to add an entry called IM. Even though CAPS LOCK is on it does not capitalize the letter i but it does the letter m and it looks like this iM. Is there anyway to get around this? Thanks for your help. ...

Re-install a database/company after deleting the DB in SQL EM.
Can anyone list all the tables that need to be cleaned out if a company database is deleted in Enterprise Manager before the company is deleted in GP? I have mistakenly done this and would like to clear everything out and set up that same database name again. No backup yet as it was a brand-new migration database ( a poor excuse I admit) that had the migration run on it. Thought we had it by clearing out the related company entries in the SY01500, DU0000xx, SY40500, SY40502? and a few more tables, but the new company setup still bombs on the very last step of the SQL setup (loading d...

Cascade delete
Hi, I need to find out if the cascade delete option is set on any of the tables in my database. Any help is greatly appreciated. Cheers Mounilk Mounilk (mounilkadakia@hotmail.com) writes: > I need to find out if the cascade delete option is set on any of the > tables in my database. Any help is greatly appreciated. Look up the catalog view sys.foreign_keys in Books Online. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL S...

Updating table from a report query
I'm tracking production gate for several departments. I want to set one of three switch in a tblGoals for a specific departmetn based on a calculated value in a report query. The thresholds are .10, .50 and .75 there are also three cutoff dates in a table called tblGateDates. I pass the NRS (department), NCOPercent (the production goal), EndDate (gate cutoff date), and the Gate (1, 2 or 3) which is the gate I'm trying to determine the depart made or missed to a function. Something not working right because near as I can tell based on message boxes I've placed in the code ...

How to restore a deleted Outlook 2003 contacts folder
I had first-level folders called bkgs_future, 0907, 0908. I deleted the first one, and for some strange reason the others disappeared as well (althought they weren't represented as sub-folders). When I right click on Personal Folders and select properties, then click on Folders it shows: Deleted Items\Bkgs_future\0907 Deleted Items\Bkgs_future\0908 But when I click on 'deleted items' it list various folders but not these ones. How can I retrieve these folders if they aren't visible in 'deleted items'? Why did it treat them as sub-folders when it didn'...

deleting rows
I have used excel for years and have never run across this problem. this is what I can do to get an error. 1. be in any file 2. save it 3. delete a row. 4. save it After #4, it meeses up and give me an error of Modver 8.0.1.4307 Offset 0018d268 APPver8.0.1.4307 Excel.Exe any suggestions? Thanks, cp try cleaning out temp dir, reboot computer -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "charlie" <cpick88@yahoo.com> wrote in message news:032001c34b1f$82f1a610$a601280a@phx.gbl... > I have used excel for years and have never run across > this...