Hmmm...
aparently, it takes more time to rewrite schema+app to the new layout :(
While doing so I fell onto another problem, to which I cannot find any
resolve so far.
1. partial index asuring ssn unique for sender work fine.
2. but in the original schema I did have an additional field NEXT, which
allowed me to dasy-chain all messages originating from a particular
sender, and just one message (the most recent one) did have it a NULL
there, so it was easy to peek the last message (which is a frequent
operation).
3. currently, having just partially-unique index on messages-persons
table for senders, I'm unable to FK (person,role,next) to (person,role,ssn).
Postgres complains, that FK columns MUST have an unconditional unique
index at its target columns.
A) how to get around it?
B) why that unique index at the target have to be unconditional? I mean:
--> if the table was split into two inharited tables (one for
role-sender, one for role-recepient), the partition table containing
only role-sender could have a full-unique index and thus could become
target for FK(sender,next).
--> so why rdbms cannot treat partial indexes just like that: as if
those where full-unique-indexes, but only covering part of the data. And
consequently if there was a 'partial-index-miss', the target key is
assumed as not present.
I'd apreciate any help in how should I implement the chaining of rows in
messages-persons table (like above); and some info on the "theory of
rdbms" (or clasure in standard specs) which lead to restrictions
preventing partial indexes as FK targets,
thenx
-R