Howto delete FK and all dependent table entries?

Question

This is the case. There are 3 tables - state, region and city. If I delete a state with the name "France", I want that all regions and cities belonging to this state be deleted as well. How to do this in T-SQL?

Answer 1

You can set up the tables for cascading deletes, so you delete the entry from the table where everything is foreign keyed to.

Answer 2

The simplest solution is to ensure that you have Cascade Delete enabled on the relationships between the tables.

Create Table State
(
    Code char(2) not null Primary Key
    , ...
)
Create Table Region
(
    Code varchar(10) not null
    , StateCode char(2) not null
    , Constraint FK_Region_State 
        Foreign Key ( StateCode )
        References State( Code )
        On Delete Cascade
)
Create Table City
(
    Name varchar(40) not null
    , StateCode char(2) not null
    , RegionCode varchar(10) not null
    , Constraint FK_City_State 
        Foreign Key ( StateCode )
        References State( Code )
        On Delete Cascade
    , Constraint FK_City_Region
        Foreign Key ( StateCode )
        References State( Code )
        On Delete Cascade
)

If for some reason you cannot enable Cascade Delete, then you will have to enforce this rule via a Trigger on the State table. (Btw, a "State" named France?)

Answer 3

I'm assuming you haven't set up cascading deletes, so you've got to work your way from the bottom up: delete cities first, then regions, then state.

delete from c
    from city c
        inner join region r
            on c.region_id = r.region_id
        inner join state s
            on r.state_id = s.state_id
    where s.state = 'France'

delete from r
    from region r
        inner join state s
            on r.state_id = s.state_id
    where s.state = 'France'

delete from s
    from state s
    where s.state = 'France'

Answer 4

Although Cascade-Delete is the way to go here, I am very uncomfortable setting that up. It feels like a very unsafe thing to do, so I never set up cascading deletes.

I prefer to do it in TSQL

DELETE FROM Cities
WHERE RegionId IN 
(
    SELECT Id
    From Regions
    Where CountryId IN
    (
        Select Id
        From Country
        Where Country = 'France'
    )
)

then delete regions

DELETE FROM Regions
Where RegionId IN
(
    Select Id
    From Country
    Where Country = 'France'
)

then delete countries

DELETE FROM Country
Where Country = 'France'