Re: partial "on-delete set null" constraint
От | Rafal Pietrak |
---|---|
Тема | Re: partial "on-delete set null" constraint |
Дата | |
Msg-id | 54A6CD82.3080704@ztk-rp.eu обсуждение исходный текст |
Ответ на | Re: partial "on-delete set null" constraint (Adrian Klaver <adrian.klaver@aklaver.com>) |
Ответы |
Re: partial "on-delete set null" constraint
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
Список | pgsql-general |
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: ------------------------------------- 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. Apparently I don't know how to do that. I'd appreciate any help. -R
В списке pgsql-general по дате отправления:
Следующее
От: Merlin MoncureДата:
Сообщение: Re: extra function calls from query returning composite type