I'm used to check the ref. integrity checkbox when making relations, but not the checkboxes below that setting. So people have to manually delete the "many"-records, before they can delete the main record. I noticed in an inherited mdb that all 3 ref. integrity check boxes are checked. Is this common practice and is it recommended? Or can I run into problems with that?
> I'm used to check the ref. integrity checkbox when making relations, but > not the checkboxes below that setting. So people have to manually delete > the "many"-records, before they can delete the main record. I noticed in > an inherited mdb that all 3 ref. integrity check boxes are checked. Is > this common practice and is it recommended? Or can I run into problems > with that?
It is a fairly common practice, and I think it's recommended on a case by case basis. For example if you have some type of purchase order system or even an invoice system in which the invoice can and will be deleted, then you want the child reocreds (the invoice details reocreds) to all be automatic deleted also.
You might have a simple database to keep track of your friends and their favorite foods. So the favorite foods table would be a table that's related back to table friends. When you remove or delete friends, you want all the records that represent their favor foods to also be deleted.
And, I suppose you also want that you can't add a favorite foods record unless you first had a friend's record.
So don't confuse a situation where you have a list of child records that represents repeating types of information (such as favorite foods). You also might have in this database an 3rd table that is a list of legitimate "allowable" foods that you use to drive a combo box for the farvoate foods table. In this case if you want a delete a food that's not being used very much, if one of the records in that the favorite foods is still using a particular food type, then you can NOT delete the food record yet can you?
So there's two types of relationships you're talking about here. One in which you have a table that's usually used to drive a combo box and this make things easier for the for user to select a value form that list (perhaps a color, or as mentioned a type of food). When you delete a master friend record (often called the parent record), those food reocrds are not touched nor are they to be deleted. So delete one of your friends from the database, the list of available foods to choose for the favorite food list table will not be changed.
So the list of records in the table that represents the persons favorite foods most certainly needs to be deleted when the person is deleted (so, you choose cascade delete).
However, the table of available foods will remain untouched, and no new records are added, nor deleted when you add new friends or even update the list of favorate foods that a particular friend has.
So, we have 3 tables:
tblFriends---->tblFavorateFoods----->tblFoods
So in the above we want all records in tblFavorateFoods removed when you delete a friend reocrd. However, the tblFoods table is rarely changed, and no reocrds are added, nor to be deleted when you delete a friend.
-- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKal...@msn.com
There's no one right answer. Cascading the deletes is convenient because it automates the child row deletions. But it's more dangerous because it's now easier for users to accidentally delete a lot of data. Most of the time I do not cascade deletes, preferring that the user intentionally delete all the child rows to help them "think twice" about the deletion. I've sometimes included a button to delete a parent record, but I usually make them confirm the deletion and in the message I tell them how many of each type of child row will also get deleted.
If the PK field is updateable (anything except a Counter) I often cascade updates to make parent editing easier.
"Lars Brownies" <L...@Brownies.com> wrote in message
> I'm used to check the ref. integrity checkbox when making relations, but > not the checkboxes below that setting. So people have to manually delete > the "many"-records, before they can delete the main record. I noticed in > an inherited mdb that all 3 ref. integrity check boxes are checked. Is > this common practice and is it recommended? Or can I run into problems > with that?
> I'm used to check the ref. integrity checkbox when making relations, but > not the checkboxes below that setting. So people have to manually delete > the "many"-records, before they can delete the main record. I noticed in > an inherited mdb that all 3 ref. integrity check boxes are checked. Is > this common practice and is it recommended? Or can I run into problems > with that?
> Thanks,
> Lars
I'd like to add a bit to Albert's answer, looking at " it's recommended on a case by case basis" from a slightly different perspective. You don't say whether your database is personal or used by multiple users in a business setting. If there are multiple users, you should consider not allowing cascading deletes as another line of defense against inadvertent data loss. Consider a scenario where you have a customers table as the 1 side of a 1 to many relationship with a customer orders table. If somebody were to delete current customer Jon A. Jones by mistake, instead of inactive customer Jon V. Jones with cascading deletes allowed, the current customer and all his orders would be automatically deleted. With cascading deletes blocked, the existence of orders would prevent the mistake. Or course, if there are other safeguards in place, such as a user "delete" actually resulting in rows being marked for archiving to be moved to a data warehouse by a batch process, then you need not be concerned.
>> I'm used to check the ref. integrity checkbox when making relations, but >> not the checkboxes below that setting. So people have to manually delete >> the "many"-records, before they can delete the main record. I noticed in >> an inherited mdb that all 3 ref. integrity check boxes are checked. Is >> this common practice and is it recommended? Or can I run into problems >> with that?
> It is a fairly common practice, and I think it's recommended on a case by > case basis. For example if you have some type of purchase order system or > even an invoice system in which the invoice can and will be deleted, then > you want the child reocreds (the invoice details reocreds) to all be > automatic deleted also.
> You might have a simple database to keep track of your friends and their > favorite foods. So the favorite foods table would be a table that's > related back to table friends. When you remove or delete friends, you want > all the records that represent their favor foods to also be deleted.
> And, I suppose you also want that you can't add a favorite foods record > unless you first had a friend's record.
> So don't confuse a situation where you have a list of child records that > represents repeating types of information (such as favorite foods). You > also might have in this database an 3rd table that is a list of legitimate > "allowable" foods that you use to drive a combo box for the farvoate foods > table. In this case if you want a delete a food that's not being used very > much, if one of the records in that the favorite foods is still using a > particular food type, then you can NOT delete the food record yet can you?
> So there's two types of relationships you're talking about here. One in > which you have a table that's usually used to drive a combo box and this > make things easier for the for user to select a value form that list > (perhaps a color, or as mentioned a type of food). When you delete a > master friend record (often called the parent record), those food reocrds > are not touched nor are they to be deleted. So delete one of your friends > from the database, the list of available foods to choose for the favorite > food list table will not be changed.
> So the list of records in the table that represents the persons favorite > foods most certainly needs to be deleted when the person is deleted (so, > you choose cascade delete).
> However, the table of available foods will remain untouched, and no new > records are added, nor deleted when you add new friends or even update the > list of favorate foods that a particular friend has.
> So, we have 3 tables:
> tblFriends---->tblFavorateFoods----->tblFoods
> So in the above we want all records in tblFavorateFoods removed when you > delete a friend reocrd. However, the tblFoods table is rarely changed, and > no reocrds are added, nor to be deleted when you delete a friend.
> -- > Albert D. Kallal (Access MVP) > Edmonton, Alberta Canada > pleaseNOOSpamKal...@msn.com
> I'm used to check the ref. integrity checkbox when making > relations, but not the checkboxes below that setting. So people > have to manually delete the "many"-records, before they can delete > the main record. I noticed in an inherited mdb that all 3 ref. > integrity check boxes are checked. Is this common practice and is > it recommended? Or can I run into problems with that?
1. enforce RI where the tables actually need to have the defined relationship.
2. CASCADE DELETES if and only if the child records have no relationships to any record but their parent records. Invoice Details would be a good example. They would have two parents, the Invoice table and, for example, the Inventory table. You could easily turn CASCADE DELETE ON between the Invoice and Invoice Details, but you would want it OFF between the Inventory table and the Invoice Details, because you don't want to invalidate the invoices that include purchases of that Inventory item.
3. CASCADE UPDATE is relevant only if the PK is editable. An editable PK is to me a red flag that it's probably not a good choice as PK, but it depends on what kind of table it is. A small lookup table with an editable PK is not such a big deal, though I would tend to use a text PK in that case only when I knew for certain the values would never be edited. If I thought they might be changed, I would then use an Autonumber surrogate key. With Autonumbers, CASCADE UPDATE should in ALL CASES be turned OFF, because it adds overhead but can't actually do anything, as Autonumbers can't be edited in the first place.
|
