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

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 2

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

Answer 3

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 4

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.