From: | Rafal Pietrak <rafal(at)ztk-rp(dot)eu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: multiple UNIQUE indices for FK |
Date: | 2016-03-04 13:30:52 |
Message-ID: | 56D98E0C.2040601@ztk-rp.eu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)ztk-rp(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Farber | 2016-03-04 13:59:17 | Check constraints for varchar[] and varchar[][] columns in a table |
Previous Message | Michael Omotayo Akinde | 2016-03-04 12:04:31 | Re: C function migration from 9.2 to 9.5 |