Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

question about cascading deletes

Discussion in 'Information Technology' started by Steve, Oct 27, 2005.

  1. Steve

    Steve
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Question About GGS Questions and Answers Aug 28, 2016
    Marriage And Discrimination Related Questions Blogs Mar 21, 2016
    Sikhi Questioning Faith Sikh Sikhi Sikhism Mar 3, 2016
    Entering into Sikhism: Questions on Reht Maryada. Blogs Oct 22, 2015
    A question about kes/hair? Blogs Oct 21, 2015

  3. BruceM

    BruceM
    Expand Collapse
    Guest

    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?

    "Steve" <sss@sss.com> wrote in message
    news:Oiatbyl2FHA.3788@tk2msftngp13.phx.gbl...
    >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
    >
    >
     
  4. Norman Yuan

    Norman Yuan
    Expand Collapse
    Guest

    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" <sss@sss.com> wrote in message
    news:Oiatbyl2FHA.3788@tk2msftngp13.phx.gbl...
    >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
    >
    >
     
  5. Steve

    Steve
    Expand Collapse
    Guest

    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...


    "BruceM" <bamoob@yawwhodawtcalm.not> wrote in message
    news:%23ZZnI6l2FHA.2796@tk2msftngp13.phx.gbl...
    > 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?
    >
    > "Steve" <sss@sss.com> wrote in message
    > news:Oiatbyl2FHA.3788@tk2msftngp13.phx.gbl...
    > >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
    > >
    > >

    >
    >
     
  6. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    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).
    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Steve" <sss@sss.com> wrote in message
    news:eVmtwIm2FHA.632@TK2MSFTNGP10.phx.gbl...
    > 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...
    >
    >
     

Share This Page