SQL Cascading delete without foreign keys?

Question

I have the following tables:

Country: Country_ID, CountryName
Regions: Region_ID, RegionName, Country_ID
Areas: Area_ID, AreaName, RegionID

I am not using foreign keys and not planning to do so, all I wanna do now is to be able in one query in the tableAdapter of country table to delete Country and all related regions, areas...

How?

Answer 1

Use transactions. Start a transaction, then use three DELETE statements, then COMMIT TRANSACTION statement.

Answer 2

In one query? I doubt you could.

But you could do it in a three:

delete from Areas where RegionID in (select Region_ID from Regions where Country_ID in (select Country_ID where CountryName='Somelandia'))
delete from Regions where Country_ID in (select Country_ID where CountryName='Somelandia')
delete from Country where CountryName = 'Somelandia'

That being said, I strongly recommend you reconsider using foreign key relationships and cascade delete for this purpose.

Answer 3

You could use a trigger if your database supports it and then use a transaction inside the trigger. The trigger would execute whenever a region or area is deleted.

Answer 4

You could try a stored procedure along these lines:

create proc EraseCountry
(
    @countryid int
)
as

BEGIN TRY 
BEGIN TRANSACTION 

    delete areas
    from areas 
     inner join regions on areas.region_id = regions.region_id
    where regions.countryid = @countryid

    delete regions
    where countryid = @countryid

    delete country
    where countryid = @countryid

COMMIT TRAN 
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRAN 
END CATCH
GO

Answer 5

What Jon said. Also,

Areas.RegionID and Regions.CountryID really ARE foreign keys.

Even if you don't declare them as such. Not declaring them speeds you up by a trivial amount, but it also allows you to store illegitimate values in those two columns (fields). You want to prevent invalid inserts in those columns for the same reason that you want to cascade deletes.