Re: partial "on-delete set null" constraint

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.

>>
>> Not sure about the standard, but pretty sure it will foul things up in
>> general. From the table structures above the user is identified by a
>> natural key of (username, domain). You are looking to break that key
>> by losing the username in both mailusers and mailboxes. Yet you want
>> to retain user content in mailusers. Not sure what purpose that is
>> going to serve when you have no defined means of identifying the
>> content? In my opinion, this is a use case for a surrogate key.
>
> As a sort of "audit trail". Mail message contains everything that's
> necesery to "recover" information when a "situation" arises.

Aah, so there is a 'defined means'.

>
> May be it's not the best way to do that, but currntly that's the plan:
> 1. keep the original
> 2. drop only minimal set of information, when user is discontiniued -
> currently just the username.
>
> Naturally, If I will not figure out how to setup such constraint
> automation, I'll have to revisit the initial plan (I hate to do that :)
> 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.

>
> 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.

>
>
> -R
>

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