Cascading DELETE?

  • I have a database and need to delete from 4 tables starting with the first and ending with the last ie:

    Table1

    pk_ColumnA

    ColumnB

    ColumnC

    Table2

    pk_ColumnA1

    fk_ColumnA

    ColumnB1

    ColumnC1

    Table3

    pk_ColumnA2

    fk_ColumnA1

    ColumnB2

    ColumnC2

    Table4

    fk_ColumnA2

    ColumnB3

    ColumnC3

    When I delete the pk_ColumnA row, I'd like it to cascade all the way to fk_ColumnA2 in the 4th table.

    I've looked online for a few hours and read through all the books I have and can't seem to figure this out. I think I need to create triggers from the top-down, but the example I tried didn't work, though I think it was for MySQL and not MSSSQL.

    Any help would be much appreciated! Thanks!

  • If you look under Create Table in Books Online, you'll find the syntax for creating a column constraint.

    What you're looking for is "References <table> (<column>) on delete cascade".

    Looks like this:

    create table dbo.DropMe1 (

    ID int identity primary key);

    go

    create table dbo.DropMe2 (

    ID int identity primary key,

    DropMe1ID int not null references dbo.DropMe1(ID) on delete cascade);

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I found Cascading Referential Integrity Constraints, is that too much? Also, can I set the cascade to roll three tables deep?

    Thanks again

  • Try it and see. That's the best way to really know it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply