Hi all;
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