Consider adding (more?) foreign key constraints

[lnicola]

Description:

Because of an unfortunate event, I ended up on my server with some extra accounts and rooms I wanted to delete. I used the proper (admin/v1/deactivate, admin/v1/rooms and admin/v2/rooms) APIs of Synapse, but it left me with a database that appears inconsistent:

• I had 10 GB of entries in current_state_delta_stream, state_groups_state and state_groups, but the rooms were missing from room_stats_state
• half of my rows in state_group_edges don't point to state_groups

I'd like to clean these up, but it's hard for me because I'm not familiar with the database structure and there are no constraints to help with it.

Adding some constraints would help avoid inconsistent states (cf. #11779) and even work as documentation. If you're worried about the performance impact, they could added in a disabled state.