如何删除FK和所有相关表条目?

我想你还没准备好 cascading deletes ,所以你必须自下而上:先删除城市,然后删除地区,然后删除州。

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'

虽然级联删除是这里的方法,但我很不舒服设置。这样做感觉非常不安全,所以我从未设置级联删除。

我喜欢用TSQL

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

然后删除区域

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

然后删除国家

DELETE FROM Country
Where Country = 'France'

最简单的解决方案是确保对表之间的关系启用了级联删除。

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
)

如果由于某种原因不能启用级联删除,则必须通过状态表上的触发器来强制执行此规则。(顺便问一句,一个叫法国的“国家”?

您可以设置用于级联删除的表,这样就可以从表中删除所有内容都是外键到的条目。