Prevent orphans

[Lars Brownie]

I have tblPerson related to tblAddress via a MxM table tblPersonAdres.
Referential Integrity and cascading deletes work fine. Except when I delete
a record from tblPersonAdres which links PersonA to AddressA. If that is the
only linking record where addressA's ID was present, addressA will become an
orphan, belonging to nobody. Is there some built-in option that will give a
message or should I build that myself? I would only want this option for the
Address, not for the Person.
Thanks, Lars

 

[Jeff Boyce]

Lars

Why would you want to automatically delete an address if the person using
that address moved? Wouldn't someone else move into that address? What if
the person who moved was a student, moving away to college ... would his/her
parents still need that address?

I'm not clear why you are using Cascading Deletes...


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Stefan Hoffmann]

hi Lars,

Is there some built-in option that will give a
message or should I build that myself?

No, there is no built-in solution.

A simple

DELETE FROM Address
WHERE NOT ID IN
(SELECT AdressID FROM AddressPerson)

or

DELETE FROM Address A
WHERE NOT EXISTS
(SELECT * FROM AdressPerson AP
WHERE AP.AddressID = A.ID)

as clean-up. The second is normally the faster one.


mfG
--> stefan <--

 

[Lars Brownie]

When a person (a member) is deleted and he is the only person related to a
particular address, I would like to automatically give the user the choice
to delete the address as well. Using cascading deletes I thought was handy:
when I delete an address, Access automatically asks whether all relative
records should be deleted too. Not good practice?

Lars

 

[Lars Brownie]

Btw: If I use cascading deletes on both relations and I want to delete a
person (that has a link to tblPersonAddress), Access crashes without a
message. Doesn't seem very reliable.

Lars

 

[Lars Brownie]

Thanks Stefan.

hi Lars,


No, there is no built-in solution.

A simple

DELETE FROM Address
WHERE NOT ID IN
(SELECT AdressID FROM AddressPerson)

or

DELETE FROM Address A
WHERE NOT EXISTS
(SELECT * FROM AdressPerson AP
WHERE AP.AddressID = A.ID)

as clean-up. The second is normally the faster one.


mfG
--> stefan <--

 

[Jeff Boyce]

Lars

Call me suspicious, but I'm not inclined to trust Access enough to set
Cascading Deletes for any but a very few situations. In most instances, I'd
rather control if/when a record is deleted.

But hey?! The feature's in there so it must be good, right? <g>

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP