Re: multiple UNIQUE indices for FK - Mailing list pgsql-general

Hi,


W dniu 04.03.2016 o 12:59, Francisco Olarte pisze:
> Hi Rafal:
>
> On Fri, Mar 4, 2016 at 11:44 AM, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
>> While doing so I fell onto another problem, to which I cannot find any
>> resolve so far.
> ...
>> 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).
>
> I do not recall your original schema too well, but IMO doing
> linked-lists with database records is not usually a good idea. They
> are very procedural and relational is declarative. Normally to peek at
> the last message from a chain you just declare your intentions in sql,
> typically by having a timestamp column and doing select whatever where
> whatelse order by xxx_ts desc limit 1.
>
>> 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).
>
> This is normally a sympton of your schema not being appropiately normalized.

OK. I'd apreciate some guidance here. I've sattled for a schema
suggested in this thread a fiew posts before. In short went like this:

CREATE TABLE persons(person_id primaty key, ...);
CREATE TABLE msgs_person(msg_id, person_id references
persons(person_id), rel_type, the_message_itself, primary
key(message_id, person_id,rel_type),....);

where:
person_id - sender or recepient of the message
msg_id - an ID uniquely assigned by sender
rel_type - a role a row in msgs_person table is assigned to this
particular relation: person+message; this role can be either SENDER or
RECEPIENT

Then I have a partial unique index:
CREATE UNIQUE INDX by_sender (msg_id,person_id,rel_type) where (rel_type
= SENDER);
which ensures, that a message can have just one SENDER.

And now, apart from the above, I'm trying to put a NEXT field into the
MSGS_PERSON table, so that I can (sort of):
ALTER TABLE  msgs_person ADD CONSTRAINT next_fk FOREIGN KEY
(next,person_id,rel_type) REFERENCES msgs_person(msg_id,person_id,
rel_type); ...( just for: rel_type=SENDER).

What should I do with this to have it "appropriately normalized"?

>
>> Postgres complains, that FK columns MUST have an unconditional unique
>> index at its target columns.
>
> I would expect this, a foreign key must uniquely determine a row on
> another table, postgres insures this with unconditional unique index.
> This is because FK target tables, not indexes. If you are targetting a
> conditional index probably you want another type of constraint.

But if this is so, a partial unique index should suffice, since it does
support locating of a *single* row in a table... and this should be all
that's required for FK to be consistant. right?

>
>> A) how to get around it?
>
> Do not use FK. Try to use generic constraints. Better , normalize your

"generic constraint's"? - pls elaborate regarding schema I've just
schetched above.

[-------------]
>
> Partial indexes, even indexes in general, are implementation details
> in "theory of rdbms". This theory is more matemathics, based on
> tuples, sets, and the like.  Normally FK wants unique keys as targets,
> the fact that many dbms force a unique index for these is an
> implementation detail, you can have a unique constraint by just

In that case, pls forgive my language - all I ment is that this
"implementation detail", in postgresql is in fact "enforced policy" ...
but this isn't actually the problem here. I'm quite happy with a system
that helps me avoid performence pitfalls.

The problem I see is different - and the reason I'm asking about
theoretical background of the implementation is different.

The problem is that once one accepts the requirement for a unique index
as FK target column "mandatory performance support", then I fail to see
real reazon, where *ENY* unique index shouldn't do that role too. They
are unique (within  domains of their conditions) and by definition yield
a single row for FK (or nothing); that should be sufficient for the
engine to keep data consistancy as expected, shouldn't it?

Naturally I undestand that there might be some deep reasons for
exclusion of partial indexed as FK target "selectors" - I'd apreciate
further explanations. But in case those reasons exist, I'd expect
workarounds to exist too - like "SQL idioms" - that people normally use
for cases like these. Would those "generic constraint" be be idiom?

So as I said before, I'm looking for some guidence here. (I'm really
emotionally bond to that NEXT field there :)

-R