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@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
