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.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
