Re: partial "on-delete set null" constraint

On 01/03/2015 09:05 AM, Rafal Pietrak wrote:
>
> W dniu 03.01.2015 o 16:48, Alban Hertroys pisze:
>>> On 03 Jan 2015, at 15:20, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
>>>
> [-------------------------]
>>> Yes. This is precisely the "semantics" I'm trying to put into the
>>> schema: after a username is "released" from service, all it's
>>> messages become "from unknown user".... unless thoroughly
>>> investigated :)
>> It also makes a foreign key reference unusable: There is no unique
>> parent record to match it to, so what exactly are you referencing?
>
> Nothing.

Which is doable, but:

1) Your FK on mailusers is foreign key (username, domain)
2) And domain text not null
3) And you want a 'smart' SET NULL action that only SETs NULL for
referencing fields in a FK that are NULL but not for those that are NOT
NULL.

Since 3) is not possible AFAIK you have the following options:

A) Set domain NULL, which defeats your purpose if I follow correctly.
B) Create a different FK, hence my suggestion about a surrogate key.
C) Forget about a FK and write your own trigger.
D) What you propose below. Though my experiences with RULEs have not
been happy. In Postgres 9.1+ you have INSTEAD OF triggers on VIEWs which
might be easier to work with.

>
> That's precisely my point here. I'd like to have "objects" in mailboxes
> table left "hanging around" after it's "disconnected" from service. FK
> acting like a power cord of a vacuum cleaner: when in service: hooked
> into the wall; after that vacuum cleaner stays there, only disconnected
> (and the socket can be used by others).
>
> But pondering the idea as the discussion goes, I think I'll try to use
> VIEW query rewriting capabilities, to get the "SET username=NULL; then
> DELETE" sequence encoded as an on delete rule of a view created on top
> of mailusers table.
>
> -R
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com