How to remove unreferenced rows in a "Many to One" relation?

in my db i have the following entities: Cities, Persons, Houses.

• Each Person MUST live in a City.

• Each Person CAN live in a House.

• Each Person can change or leave their Houses.

• Persons living in the same house must also live in the same city.

• Empty Houses must be destroyed.

Here's the schema:

How can I make sure that Persons living in the same House they also live in the same City?
How to destroy empty Houses? Is there a solution other than triggers?

I was thinking about a different approach:

I know it seems a stupid solution but i can use ON DELETE CASCADE on the House, so no House will remain unreferenced after City removal.

What do you think is the best way to solve this?

Thanks in advance an sorry for my english.

A Person and a House must be associated with a City, but a Person does not necessarily have to be associated with a House. If a Person is associated with a House, they must be associated with the same City.

Your second diagram is correct. Both Person and House are independently associated with City. You just need another table.

Simple, you create a type of intersection table to relate a Person to a House that also forces Person and House to refer to the same city.

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 is the PK so a Person cannot reside in more than one House but the same House may contain more than one Person.

When inserting a record into PersonHouse with Person 15 who (in Person table) is associated with City 37, the House ID must be contained in the House table and must also be associated with City 37. Any attempt to enter a Person and House associated with different cities will generate a foreign key violation.

StackExchange Database Administrators Q#116889, answer score: 2