如何删除“多对一”关系中未引用的行？

Question

在我的数据库中，我有以下实体:城市、个人、房屋。

• 每个人都必须住在城市里。
• 每个人都可以住在一间房子里。
• 每个人都可以换房子或离开自己的家。
• 住在同一所房子里的人也必须住在同一座城市。
• 空置的房屋必须被摧毁。

下面是模式：

我怎样才能确保住在同一所房子里的人也住在同一座城市？如何摧毁空房子？除了触发器之外，还有其他解决方案吗？

我在想另一种方法：

我知道这似乎是一个愚蠢的解决方案，但我可以使用删除级联在众议院，所以没有房子将保持不变后，城市搬迁。

你认为解决这个问题最好的方法是什么？

谢谢，为我的英语道歉。

Answer 1

一个人和一个房子必须与一个城市联系在一起，但是一个人不一定要和一个房子联系在一起。如果一个人与一所房子有关联，他们必须与同一座城市相关联。

你的第二个图表是正确的。人和房子都独立地与城市联系在一起。你只是需要另一张桌子。

简单地说，您可以创建一种类型的相交表，以便将一个人与一个House相关联，这也迫使Person和House引用同一个城市。

create table PersonHouse(
    PersonID int not null,
    HouseID  int not null,
    CityID   int not null,
    constraint PK_PersonHouse primary key( PersonID ),
    constraint FK_PersonHouse_Person foreign key( PersonID, CityID )
        references Person( ID, CityID ),
    constraint FK_PersonHouse_House foreign key( HouseID, CityID )
        references House( ID, CityID )
);

PersonID是PK，所以一个人不能住在一个以上的房子里，但是同一个房子可能包含一个以上的人。

当将记录插入到PersonHouse中时，Person 15 (在Person表中)与City37关联时，House ID必须包含在House表中，并且还必须与City 37相关联。任何试图进入与不同城市相关的个人和房屋将导致外键违规。