Re: partial "on-delete set null" constraint - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: partial "on-delete set null" constraint |
Date | |
Msg-id | 54A6F389.10907@aklaver.com Whole thread Raw |
In response to | Re: partial "on-delete set null" constraint (Rafal Pietrak <rafal@ztk-rp.eu>) |
Responses |
Re: partial "on-delete set null" constraint
|
List | pgsql-general |
On 01/02/2015 08:55 AM, Rafal Pietrak wrote: > > W dniu 02.01.2015 o 17:05, Adrian Klaver pisze: >> On 01/02/2015 07:45 AM, Rafal Pietrak wrote: >>> >>> W dniu 02.01.2015 o 16:03, Adrian Klaver pisze: >>>> On 01/02/2015 04:31 AM, Rafal Pietrak wrote: >>>>> >>> [--------------------] >> >> CCing the list. > > Ups, sorry - not that button clicked. > > [----------------------------] >>> But in any case, the question remains interesting for me in general: >>> >>> You say you thing "it'll foul thing up in general" - I'm qurious about >>> that. >> >> From what I see you want a semi-unique key(user, domain). Semi-unique >> in that at a point in time it is unique for a user, but over time it >> could represent various users. This is tied together by 'sort of a >> audit trail'. With out further information, I would say that is a >> frail system. > > sssory. I know. Pls let it be. really. > >> >>> >>> As you can see, I was able to "UPDATE maiboxes SET username = null" and >>> then "DELETE FROM mailusers" as a sequence of commands. There is nothing >>> wrong with that sequence. Naturally, in final implementation I'd have >>> additional FK from mailboxes(domain) to maildomains(domain), so that my >>> mailboxes table wan't "wonderaway" during the lifetime of the service >>> ... but that's programmers' responsibility - if I forget, my fault. At >>> the time of "delete from mailusers", all that is needed (required) from >>> the database, is not to set NULL colums that "although are asked to be >>> set NULL by action, they are also required to stay not null by >>> constraint". >>> >>> I'd say that: >>> 1. I don't know how to implement the sort of "relaxed on delate set >>> null" functionality programatically (btw: help apreciated) >>> 2. I tend to ask myself if it's possible to specify the database itself >>> to provide such functionality: either "automagically" - the "on delete >>> set null" action always skips columns declared as not null; or with a >>> little help from additional keword like "on delete set null nullable" >>> (or something)? >> >> Do not use a FK, just build your own trigger function that does what >> you want when you UPDATE/DELETE mailusers. > > FK are ways better self-documenting then trigger functions, but when > everything else fails ... :( > > ... I tried to suplement FK (by remowing actions on delete) with a > trigger, but it didn't work: We will need to see more information. Basically the complete schema definitions for the changed layout. All of this is interconnected, seeing just parts of it at a time makes it difficult/impossible to figure out. > ------------------------------------- > CREATE or replace FUNCTION prepare_null () returns trigger language > plpgsql as $$ begin old.username=null; return old; end; $$; > CREATE TRIGGER prepare_null BEFORE DELETE On mailusers for each row > execute procedure prepare_null(); > ------------------------------------- > > Is there a way to forcebly push the "old.username=null, throughout the > "on-update" FK chains into the all dependent tables, before constraints > are checked for DELETE operation? I would imagine, that trigger BEFORE > is "completed before"... the indicated action begins; but it isn't - the > above does not work. Hard to say without more information. My guess though is you are going to have to just eliminate the FK mailusers <--> mailboxes and create your own UPDATE and DELETE triggers to do what you want. > > Apparently I don't know how to do that. I'd appreciate any help. > > > -R > > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: