I am fine with the concept of foreign keys, but if you have a perfectly designed database, it should be really hard to insert a row in any table by definition.

What would solve this is defaulting to raise foreign key constraints errors at the end of transaction rather than when rows are inserted. I know postgres has an option for it, it should be the default