Re: multiple UNIQUE indices for FK

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-02-29 11:03:55
Message-ID: 56D4259B.5090501@ztk-rp.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

W dniu 28.02.2016 o 03:35, David G. Johnston pisze:
> W dniu 23.02.2016 o 09:39, Rafal Pietrak pisze:
> > Can anybody suggest any other way out of this mass?
>
>
> ​The only thought that sticks while reading your prose is:​
>
> ​message ----> message-person <---- person​
>
>
> ​message-person (message_id, person_id, relationship_type[sender, receiver])

Sorry for the prose. The only way I think I can explain myself about
concepts that I don't fully grasp is ... the flood of words.

But regarding the matter at hand. If I understand it correctlyl, your
suggestion for me is to have:

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),....);

I must say, that this is like my 10th version of my response to your
post ... with every iterration I've figured out more functionality that
I get from your suggestion ... and actually the message-person table is
pretty much what I currently have.

Even the FK from MOST_RECENT table, was doable, after I've suplemented
it with RELATION_TYPE field with a constant value of "SENDER", thus
hitting the index that is on the messages-person table.

The later got me thinking of SQL definition missing a way to put
constant into FK definition, like this:
... ADD CONSTRAINT messages_fk FOREIGN KEY (person_id, message_id,
"sender") REFERENCES msgs_person(person_id,message_id,rel_type);

but that's beyond this thread.

>
> Partitioning and partial indexes both have considerable limitations that
> you might need to work around. That said normalization exists for a
> reason and having multiple "person" columns in a table is a form of
> duplication that if left presents just the problems you are seeing.
>
> I suspect your SSN should fit onto the message-person table.
>
> The following doesn't make sense - if the SSN is sender unique then
> there is no expectation that a receiver would not receive two messages
> with the same SSN from different senders.

I don't get it.

Of cource it's possible to receive two messages with the same SSN.

By "sender unique" I mean, that every sender has full control of
whatever he/she wishes to use for SSN, provided that he/she does not
assign duplicates. It also means, that there is no relation between SSN
assigned by different senders and collisions *should* be expected unless
UNIQUE covers both THEM/SENDR and SSN.

Thus:
> ALTER ... msgs_to_me ADD CONSTRINT them_uniq UNIQUE (THEM,SSN);
>
> David J.

But thenx for the answer.

-R

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Frost 2016-02-29 13:31:38 Re: Only owners can ANALYZE tables...seems overly restrictive
Previous Message Weiping Qu 2016-02-29 10:12:14 Confusing with commit time usage in logical decoding