I have some thoughts on this and I think deferrable not null constraints make some sense (and I think once one gets there deferrable check constraints make some sense too). My view of the use cases though are a bit different and assume thick clients where some data may be looked up and we may want to insert partial information in real time, requiring that the information is complete before the data transaction completes. Unlike with a middleware layer or a web client, we might not want to assume that transactions are short-lived, or they might have to do with short-lived transactions and order of data coming in but we may not know the value yet and may require an insert/update routine in the acquisition of the data. These might not be cases where we are expecting to insert a new row. They might be cases where we might expect to reference an existing row.
Now, we could put in bogus data into the fkey fields, or use magic numbers like 0 to mean unassigned. But this gets into what I see as relative anti-patterns, namely using magic values when existing value of null would be semanticaly clearer.
The other option of course is to say "don't put it into the db until all variables are known!" but then I think that goes against PostgreSQL's great strength which is the programmability and the ability to take on certain middleware roles.
Best Wishes,
Chris Travers
