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

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