Re: multiple UNIQUE indices for FK

hi,

May be someone could help me with this SQL problem.

I'm still fighting with the "message system" I've explained in my
initial mail (apppended here, since some time have passed since).

Currently I tried "partial indeces" for FK, but either it's entirely
unsuitable for the purpose ... or I havent' learned how to use it this way.

Literelly I have:
CREATE TABLE msgs (me INT, them INT, ssn INT, fromme bool, ..)
CREATE UNIQUE INDEX fromme ON msgs(me,ssn) WHERE fromme = true;
CREATE UNIQUE INDEX fromthem ON msgs(them,ssn) WHERE fromme = false;
CREATE UNIQUE INDEX my_global ON msgs(me,them,ssn);

In addition to that, some othar tables "are supposed to" point to the
messages in MSGS table. Like the following table containing information
parteining to last message a sender generated.
CREATE TABLE most_recent(me INT, last INT, ...)
ALTER TABLE most_recent ADD CONSTRAINT msgs_fk FOREIGN KEY (me,last)
REFERENCES msgs (me,ssn);

only it doesn'nt work that way, since (ME,SSN) is only partially unique.

and it's uterly pointless to have MOST_RECENT use MY_GLOBAL index for
FK, since recepient (the THEM column) varies from one MOST_RECENT update
to another.

So my question is: is there a way to point (using foreign key) a record
from MOST_RECENT table, into a record within MSGS table? (provided the
uniqueness within MSGS is assured only partially depending on FROMME -
as shown above)?

any help appreciated.

-R

W dniu 23.02.2016 o 09:39, Rafal Pietrak pisze:
> Hi,
>
> For some time I'm struggling to get my schema "optimised" for a sort of
> "message exchange" (or "document circulation") system.
>
> For every record in the table of those messages I have:
> 1. SENDER
> 2. RECEIPIENT
> 3. unique (sender assigned)SN
> 4. ... and naturally all the other stuff, like the message itself,
> timestamps, etc.
>
> My plan is to have it unique-constraint against 1+3, for joins and to
> keep the "sanity bonds" in force all the time.
>
> So I figure to have:
> ALTER ...msgs ADD CONSTRINT sender_uniq UNIQUE (sender,SSN);
>
> Unfortunately all that proved to be "not so good" for application level,
> since there I "almost always" a need to select "MY" messages, which lead to:
> SELECT * FROM msgs WHERE sender = "ME" UNION ALL SELECT * FROM msgs
> WHERE receipient = "ME";
>
> Which does not look so bad, but when one has to JOIN it with other
> stuff, the application becomes "obfuscated" with complexity of those joins.
>
> So I tried other approach. A table with columns like:
> 1. ME
> 2. THEM
> 3. FROMME bool (true if ME is sender, false otherwise).
> 4. sender unique serial (SSN)
> 6. .... and the rest of it.
>
> But this time I had to partition this table (on FROMME value), to be
> able to correctly create different constraints depending on FROMME being
> true or false. So I have:
> ALTER ...msgs_from_me ADD CONSTRINT me_uniq UNIQUE (ME,SSN);
> ALTER ... msgs_to_me ADD CONSTRINT them_uniq UNIQUE (THEM,SSN);
>
> Now application level selects and joins are much simpler, like:
> SELECT * FROM msgs m JOIN partners p USING (them);
>
> But along the run, the specs for the system evolve, and currently I need
> to asssign an additional unique serial, which sequentially lables every
> message that "belongs" to ME irrespective if ME originated it or ME is a
> recepient. And it have to be explicitly unique constrained for FK.
>
> My problem is, that currently the table is partitioned.
>
> Is there a way to have a unique constraint across partitions (inharited
> tables)? And I'm not looking back to the initial (single table) schema,
> since I'm unable to sreach my head around the concept of a unique
> constraint that is able to cover IDs, which sometimes are in the SENDER
> column, while on other times in RECEPIENT.
>
> Can anybody suggest any other way out of this mass? that is, apart from
> siging off ;7
>
> Thenx,
>
> -R
>
>