Referential integrity settings

[Lars Brownies]

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

 

[Albert D. Kallal]

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.

 

[Paul Shapiro]

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.

 

[PvdG42]

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.

 

[Lars Brownies]

Thanks all for your replies! Really helpful.

Lars

 

[David W. Fenton]

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.