question about cascading deletes

[Steve]

I have setup relationships with my tables to cascade deletes and updates.
I just tried deleting a record that had an FK to another tables PK and
although the delete removed the record with the FK, it didn't also remove
the PK record. That sounds confusing.

Let's try this:

[Tbl_A]
[ProtocolID] PK

[Tbl_B]
[SomeID] PK
[ProtocolID] FK to Tbl_A


When I delete a record from Tbl_B, I want it to delete the corresponding
record in Tbl_A. Isn't that what cascade deletes are supposed to do?

Thanks for reading!
-Steve

 

[BruceM]

Let's say you are using the database to keep track of Sales Orders. There
is an Orders table and an OrderDetails table. The PK in tblOrders is
related one-to-many with a corresponding FK in tblOrder Details. If you
delete an Order, all of the details will also be deleted with a cascading
delete; otherwise you would have OrderDetails wandering around by
themselves, not associated with any order. On the other hand, if you delete
an Order Detail (let's say the customer cancelled one item from an original
Order of ten items) you will still have the original Order and the remaining
nine OrderDetails. That is as it should be.
Do you have more than one related record in tbl_B for each record in tbl_A?
If not, why have tbl_B at all?

 

[Norman Yuan]

Cascading delete is as the other way around as your understanding: you
delete the parent record, all related child records would be deleted if a
sort of cascade deleting in place.

In your case, after deleting a child record, you have to check the database
for all possible related child records (they may exist in more than one
table). You can only go on the delete the parent record if no related child
records exits.

 

[Steve]

Hi Bruce,
I understand what you mean. To answer your question, I need to introduce a
3rd table;

[Tbl_C]
[ConfigurationID] PK
[ProtocolID] FK

Many Tbl_C records will share with many Tbl_A records, hence the need for
Tbl_A.
Tbl_C records are not deleted or edited, they are constant, default sets of
data

Tbl_B has a one-to-one relationship with Tbl_A, hence the desire to cascade
deletes.

Given that explanation, I'm not sure how to solve this. I had hoped that I
could simply add two DELETE statments to my Access Query, but unlike
SQLServer stored procedures, I don't appear to be able to do that

Does that make sense to you? Database design and relationships can be a
tricky thing to communicate in words and text...

Let's say you are using the database to keep track of Sales Orders. There
is an Orders table and an OrderDetails table. The PK in tblOrders is
related one-to-many with a corresponding FK in tblOrder Details. If you
delete an Order, all of the details will also be deleted with a cascading
delete; otherwise you would have OrderDetails wandering around by
themselves, not associated with any order. On the other hand, if you delete
an Order Detail (let's say the customer cancelled one item from an original
Order of ten items) you will still have the original Order and the remaining
nine OrderDetails. That is as it should be.
Do you have more than one related record in tbl_B for each record in tbl_A?
If not, why have tbl_B at all?

I have setup relationships with my tables to cascade deletes and updates.
I just tried deleting a record that had an FK to another tables PK and
although the delete removed the record with the FK, it didn't also remove
the PK record. That sounds confusing.

Let's try this:

[Tbl_A]
[ProtocolID] PK

[Tbl_B]
[SomeID] PK
[ProtocolID] FK to Tbl_A


When I delete a record from Tbl_B, I want it to delete the corresponding
record in Tbl_A. Isn't that what cascade deletes are supposed to do?

Thanks for reading!
-Steve

 

[Van T. Dinh]

Cascade Delete, *if* enforced on a relationship, means that if a One Record
(which is also know as the Parent Record) is deleted, the database engine
will automatically delete the Many Records (also known as the Child
Records). The theory is that you cannot have children without parent.
There are lots of cases in real life where you don't want to leave Child
Records in the database where the Parent Record is removed and hence
database engine has the Cascade Delete facility to ensure that there are no
orphaned Child Records in the database.

In you case you can enforce Cascade Delete on the relationship between A and
B but don't enforce the the Cascade Delete between A and C.

If you want to delete Records from both Tables, you can simply write a Sub
(in VBA) to execute 2 Delete Queries / SQL Strings. In fact, I rarely use
Cascade Delete (too easy to delete, I think) and I tend to write code to
delete Child Records and then Parent Record(s).