Re: partial "on-delete set null" constraint

W dniu 03.01.2015 o 16:07, Adrian Klaver pisze:
> On 01/03/2015 12:49 AM, Rafal Pietrak wrote:
>>
[---------------------]
>>
>> With TRIGGER alone (i.e. without "documenting FK"), one will have to
>> analize the body of an "ever growing" function. Which at certain point
>> would become too much of an effort, and "new tools" will be created as
>> needed.... leading to a spaghetti code. I'd like to provide environment
>> that helps avoiding that.
>
> That is what documentation is for:) You also can add COMMENTs to

Ouch. That one hurt ;7

[-----------------------]
>> DELETE FROM mailusers ;
>> ERROR: update or delete on table "mailusers" violates foreign key
>> constraint "mailboxes_username_fkey" on table "mailboxes"
>> details: Key (username, domain)=(postmaster, example.com) is still
>> referenced from table "mailboxes".
>> ----------------------------
>
> Honestly I do not know the timing of FK checks, but I for one would
> not rely on a function that tries to 'game' the system. The house can
> change the rules.

Frankly I wasn't going towards gaming the system, but to check if there
are "controls" that I can use.

>
>>
>> Is there a way to write a trigger function that "prepares data" of
>> relevant tables by making sure, any existing FKs are no longer violated
>> (like in the above testcase) at the time the actual statement (that
>> would violate them) executes?
>
> Not that I know of. I know you do not want to hear it, but you are
> trying to go against the flow of RI. If you want to do that you are
> going to have to roll your own code and drop the FK. Me personally I
> would move the mailboxes data into a 'history' table on deletion of a
> mailusers. In said history table there would be a serial column set as
> the PK so there would be no (username,domain) conflict and complete
> information would be retained.

Yes. I gather, that's what's ahead of me. In fact, after that discussion
I'm more towards setting aside some dummy prefixing scheme for
usernames, which would invalidate them when discontinued, while
maintaining them as reference keys within mailuser table. Yet, its pity
my original "clever" plan didn't worked eventually.

Thenx, all the same.

-R