SQL Server Performance

cascade delete from a primary table to its foreign table and then to another primary table

Discussion in 'SQL Server 2008 General DBA Questions' started by WingSzeto, Jan 20, 2011.

  1. WingSzeto Member

    I have an intermediate table (foregin key table) that is used to linked to two primiary tables. I list the tables below.
    TableA (PrimaryKeyA, nameA)
    TableB (PrimaryKeyB, nameB)
    TableIntermed(PriimaryKeyA, PrimaryKeyB) PriimaryKeyA and PrimaryKeyB is the foregin key to its respective table and has a cascade delete on accordingly.
    I want this to happen.
    When a record in TableA is deleted, the corresponding records in TableIntermed are also deleted. Plus the record in table B that is tied to the deleted record in tablIntermed is also deleted. The first part of delete is handled through cascade delete, but how about the second part delete for table B? Is the trigger the only way to do that?
    w
  2. RamJaddu Member

    If you want to delete them immediately use triggers but please take care when you impliment triggers on production servers they should be tested throughly.
    Alternatively you can scheduled cleanup scirpts normally overnight.
  3. FrankKalis Moderator

    Another alternative to control the whole action would be to use a stored procedure for this access.
  4. satya Moderator

    ON DELETE CASCADE is the magic word for this, you can use this by setting as a constraint such as:
    • set the constraint TBLE_FKCONST with the ON DELETE CASCADE
      ALTER TABLE TBLE ADD CONSTRAINT TBLE_FKCONST FOREIGN KEY (ForgKey) REFERENCES (Tbl_B) ON DELETE CASCADE
    Also books online si your best resource to go through few code examples and explanation.
    FYI http://support.microsoft.com/kb/142480
  5. mmarovic Active Member

    Hi Satya.As Wing said himself, he is aware of the solution you mentioned that deletes related rows in link table when row in tbl_a is deleted. However, he wanted to have row in tbl_B deleted as well when row in table A is deleted.As Frank said, store procedure is the right place to solve that type of business logic.However, that design is odd, it might not be quite correct and there are several questions you need to answer before applying solution:Can corresponding row in tbl_B be linked to another (not deleted) row in tbl_A? If so should also that other row be deleted from tbl_A? and all rows in link table corresponding to another row in tbl_A? and corresponding rows in tabl_B, etc, etc...?If there is always just one corresponding row in tbl_B (linked via link table), then instead of having linked table, better add tbl_B pk column(s) in tbl_A and add foreign key on that column.
  6. satya Moderator

    Mirko, good to see you back here after a long time [:)]..welcome back.
    On the topic I would say it requires a design change to ensure the required tasks can be acccomplished, I'm not saying to go with default methods in this case and agree with Frank too that SP is helpful, before to see any surprises on that end I would recommend a design change in tables to keep the DRI intact.
  7. mmarovic Active Member

    Thank you Satya, nice to see you, Frank and Luis still active and helping people. :)
  8. FrankKalis Moderator

    Mirko, it surely is nice to "see" you back again! [:)]

Share This Page