What are the disadvantages when I use mutually dependent foreign keys?

I know that it’s not a good method to use two tables with mutual foreign key definitions, but I don’t understand the difficulties that arise in the database. An example: I’ve a table city and a table state. City.city points at state.capitalcity and state.state points at city.state. I would appreciate any help, thanks.

Answer

That’s fine. The problem arises when the keys are NOT NULL. In that case,

• You can’t insert a new state, because the capitalcity needs to exist.
• Your can’t insert a the capitalcity, because the state needs to exist.

Oops! That makes it a little hard load any data at all.

Of course, you can get around this by dropping or delaying the foreign key constraints.

You don’t have this issue if one or either of these is NULLable — such as the capitalcity.