Thread: partial "on-delete set null" constraint

Hello,

Rewriting my mail-hub I fell into the following problem:
1. I have a table with mailmessages, which has an FK to a table of hub
users.
2. I'd like to retain the content of message repository (with it's
domain key not cleared), when I drop a particular username from service
.... to release that username to others.
3. I try to do that with FK "on-update/on-delete" actions, but to no avail:

testcase-------(against postgresql v9.1 hosted by
debian)---------------------------
CREATE TABLE maildomains (domain text primary key, profile text not null);
CREATE  TABLE mailusers (username text , domain text references
maidomains(domain), primary key (username, domain));
CREATE  TABLE mailboxes (username text , domain text not null,
mailmessage text not null , foreign key (username, domain) references
mailusers (username,domain) on update cascade on delete set null);

INSERT  INTO  maildomains (domain, profile ) VALUES ('example.com',
'active');
INSERT  INTO  mailusers (username,domain) VALUES ('postmaster',
'example.com');
INSERT  INTO  mailboxes (username,domain, mailmessage) VALUES
('postmaster', 'example.com', 'Hello');

DELETE FROM mailusers ;
===>>> ERROR:  SQL "UPDATE ONLY "public"."mailboxes"........... etc...

But:
UPDATE  mailboxes SET username = null;
DELETE FROM mailusers ;
===>>> OK!!!

SELECT * from mailboxes ;
  username |   domain    | mailmessage
----------+-------------+--------------
           | example.com | Hello
------------------------------END testcase

I tried a TRIGGER BEFORE DELETE on table mailuser to set the username
being deleted to NULL (and simulate the above OK example part), but the
update does not propagate along constraints before constraint error is
detected and the whole delete get aborted by postgres.

Is there a way to implement that sort of referrential constraints (i.e.:
just partially "set null on delete")?

Would it violate SQL standard (signifficantly), if an "on delete set
null" action just ignored all the FK columns that have a "NOT NULL"
constraint set?

Thenx,

-R

On 01/02/2015 04:31 AM, Rafal Pietrak wrote:
> Hello,
>
> Rewriting my mail-hub I fell into the following problem:
> 1. I have a table with mailmessages, which has an FK to a table of hub
> users.
> 2. I'd like to retain the content of message repository (with it's
> domain key not cleared), when I drop a particular username from service
> .... to release that username to others.
> 3. I try to do that with FK "on-update/on-delete" actions, but to no avail:
>
> testcase-------(against postgresql v9.1 hosted by
> debian)---------------------------
> CREATE TABLE maildomains (domain text primary key, profile text not null);
> CREATE  TABLE mailusers (username text , domain text references
> maidomains(domain), primary key (username, domain));
> CREATE  TABLE mailboxes (username text , domain text not null,
> mailmessage text not null , foreign key (username, domain) references
> mailusers (username,domain) on update cascade on delete set null);
>
> INSERT  INTO  maildomains (domain, profile ) VALUES ('example.com',
> 'active');
> INSERT  INTO  mailusers (username,domain) VALUES ('postmaster',
> 'example.com');
> INSERT  INTO  mailboxes (username,domain, mailmessage) VALUES
> ('postmaster', 'example.com', 'Hello');
>
> DELETE FROM mailusers ;
> ===>>> ERROR:  SQL "UPDATE ONLY "public"."mailboxes"........... etc...
>
> But:
> UPDATE  mailboxes SET username = null;
> DELETE FROM mailusers ;
> ===>>> OK!!!
>
> SELECT * from mailboxes ;
>   username |   domain    | mailmessage
> ----------+-------------+--------------
>            | example.com | Hello
> ------------------------------END testcase
>
> I tried a TRIGGER BEFORE DELETE on table mailuser to set the username
> being deleted to NULL (and simulate the above OK example part), but the
> update does not propagate along constraints before constraint error is
> detected and the whole delete get aborted by postgres.
>
> Is there a way to implement that sort of referrential constraints (i.e.:
> just partially "set null on delete")?
>
> Would it violate SQL standard (signifficantly), if an "on delete set
> null" action just ignored all the FK columns that have a "NOT NULL"
> constraint set?

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.

>
> Thenx,
>
> -R
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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@aklaver.com

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

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

W dniu 02.01.2015 o 20:37, Adrian Klaver pisze:
> On 01/02/2015 08:55 AM, Rafal Pietrak wrote:
[------------------]
>>
>> 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

I'll put a complete testcase at the end of this mail. It'll not be the
entire schema, to focus on the case at hand and avoid obfuscation of a
problem.

> eliminate the FK mailusers <--> mailboxes and create your own UPDATE
> and DELETE triggers to do what you want.

Yes, I could. But the thing is in the future lifetime of the system.

With FK, when extending the system in the future (possibly by others), a
simple look at details of MAILBOXES table gives guidance on how to add
something similar (like internal tweets/broadcasts/etc).

With TRIGGER alone (i.e. without "documenting FK"), one will have to
analize the body of an "ever growing" function. Which at certain point
would become too much of an effort, and "new tools" will be created as
needed.... leading to a spaghetti code. I'd like to provide environment
that helps avoiding that.

In other words, I hope to keep FK as "constraints of data by design",
that not neceserly is to be maintained by the database engine (by FK
triggers), but which will help programmers write supplementary
functions/triggers which do, what's necessary to keep that consistency.
(that particular functionality could be satisfied if FK actions "on
delete set null" skipped columns with "not null" attribute, but I
understand that this is not available/feasible).

So I try to write such supplementary trigger, while keeping the FK present.

This gets us  back to my testcase:

---------test schema-----------------------
CREATE TABLE maildomains (domain text primary key, profile text not null);
CREATE  TABLE mailusers (username text , domain text references
maildomains(domain) on update cascade, primary key (username, domain));
CREATE  TABLE mailboxes (username text, domain text not null,
mailmessage text not null , foreign key (username, domain) references
mailusers (username,domain) on update cascade);
--------------------------------

----------test data-------------
INSERT  INTO  maildomains (domain, profile ) VALUES ('example.com',
'active');
INSERT  INTO  mailusers (username,domain) VALUES ('postmaster',
'example.com');
INSERT  INTO  mailboxes (username,domain, mailmessage) VALUES
('postmaster', 'example.com', 'Hello');
----------------------------------

-------------the goal functionality ...  doesnt work at the
moment--------------------
DELETE FROM mailusers ;
ERROR:  update or delete on table "mailusers" violates foreign key
constraint "mailboxes_username_fkey" on table "mailboxes"
details:  Key (username, domain)=(postmaster, example.com) is still
referenced from table "mailboxes".
--------------------------------------------------------

But an application could do
---------a successfull scenario with expected result-------------------
testvm=# UPDATE  mailboxes SET username = null;
UPDATE 1
testvm=# DELETE FROM mailusers ;
DELETE 1
-----------------------------------------------------------
Which works just fine.

So I add a TRIGER BEFORE, to have the above first statement get executed
just like in the above example: before the actual DELETE:
----------------------
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();
-----------------------------

Yet, it doesn't work that way:
------------------------------
INSERT  INTO  mailusers (username,domain) VALUES ('postmaster',
'example.com');
INSERT  INTO  mailboxes (username,domain, mailmessage) VALUES
('postmaster', 'example.com', 'Hello');
DELETE FROM mailusers ;
ERROR:  update or delete on table "mailusers" violates foreign key
constraint "mailboxes_username_fkey" on table "mailboxes"
details:  Key (username, domain)=(postmaster, example.com) is still
referenced from table "mailboxes".
----------------------------

Is there a way to write a trigger function that "prepares data" of
relevant tables by making sure, any existing FKs are no longer violated
(like in the above testcase) at the time the actual statement (that
would violate them) executes?

-R

On 02 Jan 2015, at 13:31, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
>
> Hello,
>
> Rewriting my mail-hub I fell into the following problem:
> 1. I have a table with mailmessages, which has an FK to a table of hub users.
> 2. I'd like to retain the content of message repository (with it's domain key not cleared), when I drop a particular
usernamefrom service .... to release that username to others. 
> 3. I try to do that with FK "on-update/on-delete" actions, but to no avail:
>
> testcase-------(against postgresql v9.1 hosted by debian)---------------------------
> CREATE TABLE maildomains (domain text primary key, profile text not null);
> CREATE  TABLE mailusers (username text , domain text references maidomains(domain), primary key (username, domain));
> CREATE  TABLE mailboxes (username text , domain text not null, mailmessage text not null , foreign key (username,
domain)references mailusers (username,domain) on update cascade on delete set null); 

You assumed a functional dependency between username and domain, while those fields actually describe independent
entitiesthat don’t necessarily go together as you found out. Hence you need to normalise further. 

For example:

CREATE TABLE maildomains (domain text primary key, profile text not null);
CREATE  TABLE mailusers (username text primary key);
CREATE  TABLE maildomainusers (username text references mailusers(username), domain text references
maildomains(domain),primary key (username, domain)); 
CREATE  TABLE mailboxes (username text references mailusers(username) on update cascade on delete set null, domain text
notnull references maildomains(domain) on update cascade, mailmessage text not null); 

> Is there a way to implement that sort of referrential constraints (i.e.: just partially "set null on delete”)?

Not as a foreign key reference delete action.

> Would it violate SQL standard (signifficantly), if an "on delete set null" action just ignored all the FK columns
thathave a "NOT NULL" constraint set? 

Yes. You would end up with a non-unique reference to the foreign table, as the tuple (domain, NULL) could reference
_any_mailuser in a domain: NULL means ‘unknown’, any username might match that. 

As I understand it, this is precisely why Boyce-relationality forbids NULLs in primary keys, although I’m not so sure
he’sright about that. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

W dniu 03.01.2015 o 14:11, Alban Hertroys pisze:
[------------------]
> You assumed a functional dependency between username and domain, while those fields actually describe independent
entitiesthat don’t necessarily go together as you found out. Hence you need to normalise further. 
>
> For example:
>
> CREATE TABLE maildomains (domain text primary key, profile text not null);
> CREATE  TABLE mailusers (username text primary key);
> CREATE  TABLE maildomainusers (username text references mailusers(username), domain text references
maildomains(domain),primary key (username, domain)); 
> CREATE  TABLE mailboxes (username text references mailusers(username) on update cascade on delete set null, domain
textnot null references maildomains(domain) on update cascade, mailmessage text not null); 

I don't think that this tableset actually describe "an ordinary
mailhub", which I'm coding.

the "on delete set null" within mailboxes(username) act only on delete
executed at mailusers; while the delete in question will be executed on
maildomainusers.

In particular "postmaster", as a single entity in mailusers table, will
have as many entries in maildomainusers as there are domains in
maildomains. But some domains may live without a postmaster user ... or
a postmaster user may be replaced by an alias (another table, not
presented for clearity). in such case, postmaster user will be dropped
from maildomainusers, but will remain in mailusers table for other
domains to reference. And delete of that postmaster user from
maildomainuser will not fireback into the mailboxes to set null
postmaster username from mails within that domain.

This additional level of normalization solves me anything, I think.

>
>> Is there a way to implement that sort of referrential constraints (i.e.: just partially "set null on delete”)?
> Not as a foreign key reference delete action.

Pity. So I must look for some sort of trigger functions .... as I've
already started, but nothing came up functioning as I'd need it to.

>
>> Would it violate SQL standard (signifficantly), if an "on delete set null" action just ignored all the FK columns
thathave a "NOT NULL" constraint set? 
> Yes. You would end up with a non-unique reference to the foreign table, as the tuple (domain, NULL) could reference
_any_mailuser in a domain: NULL means ‘unknown’, any username might match that. 

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 :)


>
> As I understand it, this is precisely why Boyce-relationality forbids NULLs in primary keys, although I’m not so sure
he’sright about that. 
>

Having only slight theoretical background, I'd say: it could be
"partially" the reason. I think, that "primary key" is just a syntactic
shortcut for "unique AND not null" - so often used, that the shortcut is
so appreciated. But "just unique", meaning unique just for values that
"happen to be known" is also usefull, and thus it is allowed on equal
bases.... only for other usage scenarios.

-R

On 01/03/2015 12:49 AM, Rafal Pietrak wrote:
>
> W dniu 02.01.2015 o 20:37, Adrian Klaver pisze:
>> On 01/02/2015 08:55 AM, Rafal Pietrak wrote:
> [------------------]
>>>
>>> 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
>
> I'll put a complete testcase at the end of this mail. It'll not be the
> entire schema, to focus on the case at hand and avoid obfuscation of a
> problem.
>
>> eliminate the FK mailusers <--> mailboxes and create your own UPDATE
>> and DELETE triggers to do what you want.
>
> Yes, I could. But the thing is in the future lifetime of the system.
>
> With FK, when extending the system in the future (possibly by others), a
> simple look at details of MAILBOXES table gives guidance on how to add
> something similar (like internal tweets/broadcasts/etc).
>
> With TRIGGER alone (i.e. without "documenting FK"), one will have to
> analize the body of an "ever growing" function. Which at certain point
> would become too much of an effort, and "new tools" will be created as
> needed.... leading to a spaghetti code. I'd like to provide environment
> that helps avoiding that.

That is what documentation is for:) You also can add COMMENTs to
objects(www.postgresql.org/docs/9.3/interactive/sql-comment.html).
Besides if the action is sufficiently similar I could see developing a
generic function.

>
> In other words, I hope to keep FK as "constraints of data by design",
> that not neceserly is to be maintained by the database engine (by FK
> triggers), but which will help programmers write supplementary
> functions/triggers which do, what's necessary to keep that consistency.
> (that particular functionality could be satisfied if FK actions "on
> delete set null" skipped columns with "not null" attribute, but I
> understand that this is not available/feasible).
>
> So I try to write such supplementary trigger, while keeping the FK present.
>
> This gets us  back to my testcase:
>
> ---------test schema-----------------------
> CREATE TABLE maildomains (domain text primary key, profile text not null);
> CREATE  TABLE mailusers (username text , domain text references
> maildomains(domain) on update cascade, primary key (username, domain));
> CREATE  TABLE mailboxes (username text, domain text not null,
> mailmessage text not null , foreign key (username, domain) references
> mailusers (username,domain) on update cascade);
> --------------------------------
>
> ----------test data-------------
> INSERT  INTO  maildomains (domain, profile ) VALUES ('example.com',
> 'active');
> INSERT  INTO  mailusers (username,domain) VALUES ('postmaster',
> 'example.com');
> INSERT  INTO  mailboxes (username,domain, mailmessage) VALUES
> ('postmaster', 'example.com', 'Hello');
> ----------------------------------
>
> -------------the goal functionality ...  doesnt work at the
> moment--------------------
> DELETE FROM mailusers ;
> ERROR:  update or delete on table "mailusers" violates foreign key
> constraint "mailboxes_username_fkey" on table "mailboxes"
> details:  Key (username, domain)=(postmaster, example.com) is still
> referenced from table "mailboxes".
> --------------------------------------------------------
>
> But an application could do
> ---------a successfull scenario with expected result-------------------
> testvm=# UPDATE  mailboxes SET username = null;
> UPDATE 1
> testvm=# DELETE FROM mailusers ;
> DELETE 1
> -----------------------------------------------------------
> Which works just fine.
>
> So I add a TRIGER BEFORE, to have the above first statement get executed
> just like in the above example: before the actual DELETE:

Except it is not the same thing. In the above you execute two
statements, one UPDATE(which is actually a DELETE/INSERT) and then a
DELETE. In the below you try to do everything in one statement.

> ----------------------
> 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();
> -----------------------------
>
> Yet, it doesn't work that way:
> ------------------------------
> INSERT  INTO  mailusers (username,domain) VALUES ('postmaster',
> 'example.com');
> INSERT  INTO  mailboxes (username,domain, mailmessage) VALUES
> ('postmaster', 'example.com', 'Hello');
> DELETE FROM mailusers ;
> ERROR:  update or delete on table "mailusers" violates foreign key
> constraint "mailboxes_username_fkey" on table "mailboxes"
> details:  Key (username, domain)=(postmaster, example.com) is still
> referenced from table "mailboxes".
> ----------------------------

Honestly I do not know the timing of FK checks, but I for one would not
rely on a function that tries to 'game' the system. The house can change
the rules.

>
> Is there a way to write a trigger function that "prepares data" of
> relevant tables by making sure, any existing FKs are no longer violated
> (like in the above testcase) at the time the actual statement (that
> would violate them) executes?

Not that I know of. I know you do not want to hear it, but you are
trying to go against the flow of RI. If you want to do that you are
going to have to roll your own code and drop the FK. Me personally I
would move the mailboxes data into a 'history' table on deletion of a
mailusers. In said history table there would be a serial column set as
the PK so there would be no (username,domain) conflict and complete
information would be retained.

>
> -R
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

W dniu 03.01.2015 o 16:07, Adrian Klaver pisze:
> On 01/03/2015 12:49 AM, Rafal Pietrak wrote:
>>
[---------------------]
>>
>> With TRIGGER alone (i.e. without "documenting FK"), one will have to
>> analize the body of an "ever growing" function. Which at certain point
>> would become too much of an effort, and "new tools" will be created as
>> needed.... leading to a spaghetti code. I'd like to provide environment
>> that helps avoiding that.
>
> That is what documentation is for:) You also can add COMMENTs to

Ouch. That one hurt ;7

[-----------------------]
>> DELETE FROM mailusers ;
>> ERROR:  update or delete on table "mailusers" violates foreign key
>> constraint "mailboxes_username_fkey" on table "mailboxes"
>> details:  Key (username, domain)=(postmaster, example.com) is still
>> referenced from table "mailboxes".
>> ----------------------------
>
> Honestly I do not know the timing of FK checks, but I for one would
> not rely on a function that tries to 'game' the system. The house can
> change the rules.

Frankly I wasn't going towards gaming the system, but to check if there
are "controls" that I can use.

>
>>
>> Is there a way to write a trigger function that "prepares data" of
>> relevant tables by making sure, any existing FKs are no longer violated
>> (like in the above testcase) at the time the actual statement (that
>> would violate them) executes?
>
> Not that I know of. I know you do not want to hear it, but you are
> trying to go against the flow of RI. If you want to do that you are
> going to have to roll your own code and drop the FK. Me personally I
> would move the mailboxes data into a 'history' table on deletion of a
> mailusers. In said history table there would be a serial column set as
> the PK so there would be no (username,domain) conflict and complete
> information would be retained.

Yes. I gather, that's what's ahead of me. In fact, after that discussion
I'm more towards setting aside some dummy prefixing scheme for
usernames, which would invalidate them when discontinued, while
maintaining them as reference keys within mailuser table. Yet, its pity
my original "clever" plan didn't worked eventually.

Thenx, all the same.


-R

> On 03 Jan 2015, at 15:20, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
>
> W dniu 03.01.2015 o 14:11, Alban Hertroys pisze:
> [------------------]
>> You assumed a functional dependency between username and domain, while those fields actually describe independent
entitiesthat don’t necessarily go together as you found out. Hence you need to normalise further. 
>>
>> For example:
>>
>> CREATE TABLE maildomains (domain text primary key, profile text not null);
>> CREATE  TABLE mailusers (username text primary key);
>> CREATE  TABLE maildomainusers (username text references mailusers(username), domain text references
maildomains(domain),primary key (username, domain)); 
>> CREATE  TABLE mailboxes (username text references mailusers(username) on update cascade on delete set null, domain
textnot null references maildomains(domain) on update cascade, mailmessage text not null); 
>
> I don't think that this tableset actually describe "an ordinary mailhub", which I'm coding.

An “ordinary mail hub” is rather subject to interpretation, so that depends on your definition of it. As I understand
it,your “mail hub” collects mails from several domains for various users? I’m not really sure about the benefits of
suchan application, unless internet connections to the domains you’re playing hub for are really flaky - but that’s
justa guess. 

> the "on delete set null" within mailboxes(username) act only on delete executed at mailusers; while the delete in
questionwill be executed on maildomainusers. 

It was but an example I cooked up quickly from the info you provided. Yeah, you would have to set the username
referenceto NULL by hand if you’d delete maildomainusers. That could easily be done using a trigger on maildomainusers,
though.

> In particular "postmaster", as a single entity in mailusers table, will have as many entries in maildomainusers as
thereare domains in maildomains. But some domains may live without a postmaster user ... or a postmaster user may be
replacedby an alias (another table, not presented for clearity). in such case, postmaster user will be dropped from
maildomainusers,but will remain in mailusers table for other domains to reference. And delete of that postmaster user
frommaildomainuser will not fireback into the mailboxes to set null postmaster username from mails within that domain. 

That description makes your problem a lot easier to envision.

> Pity. So I must look for some sort of trigger functions .... as I've already started, but nothing came up functioning
asI'd need it to. 
>
>>
>>> Would it violate SQL standard (signifficantly), if an "on delete set null" action just ignored all the FK columns
thathave a "NOT NULL" constraint set? 
>> Yes. You would end up with a non-unique reference to the foreign table, as the tuple (domain, NULL) could reference
_any_mailuser in a domain: NULL means ‘unknown’, any username might match that. 
>
> 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
youreferencing? 

Besides, with the schema you gave, “unless thoroughly investigated” is not going to help much to find the user; that
informationis no longer present unless you also store it elsewhere (for example inside your mailbox message data). 

>>
>> As I understand it, this is precisely why Boyce-relationality forbids NULLs in primary keys, although I’m not so
surehe’s right about that. 
>>
>
> Having only slight theoretical background, I'd say: it could be "partially" the reason. I think, that "primary key"
isjust a syntactic shortcut for "unique AND not null" - so often used, that the shortcut is so appreciated. But "just
unique",meaning unique just for values that "happen to be known" is also usefull, and thus it is allowed on equal
bases....only for other usage scenarios. 

I’m in the middle of (finally) receiving that theoretical background, so I know where you come from. I’m also in the
fortunateposition to have all that theoretical jargon at the ready ;) 

Until recently I used to think the same way about NULLs in PK's, and it holds true when you only look at the PK.
However, once you add foreign key references to a table with such a PK, things change. FK’s are supposed to reference a
singleunique entity in a parent table, but when there are NULLs in the mix, that becomes impossible. 

Cheers,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

W dniu 03.01.2015 o 16:48, Alban Hertroys pisze:
>> On 03 Jan 2015, at 15:20, Rafal Pietrak <rafal@ztk-rp.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
youreferencing? 

Nothing.

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

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@ztk-rp.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.klaver@aklaver.com

> On 03 Jan 2015, at 14:11, Alban Hertroys <haramrae@gmail.com> wrote:
>
> On 02 Jan 2015, at 13:31, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
>> Would it violate SQL standard (signifficantly), if an "on delete set null" action just ignored all the FK columns
thathave a "NOT NULL" constraint set? 
>
> As I understand it, this is precisely why Boyce-relationality forbids NULLs in primary keys, although I’m not so sure
he’sright about that. 

Well, that’s embarrassing, it’s only a few weeks since I learned this and I’m already attributing the theory to the
wrongdatabase deity! That’s Codd-relationality, of course. Not Boyce. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

> On 03 Jan 2015, at 18:05, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
> W dniu 03.01.2015 o 16:48, Alban Hertroys pisze:
>>> On 03 Jan 2015, at 15:20, Rafal Pietrak <rafal@ztk-rp.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
youreferencing? 
>
> Nothing.
>
> 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;
afterthat vacuum cleaner stays there, only disconnected (and the socket can be used by others). 

Then you should make that reference to maildomains(domain) also NULLable. Otherwise you aren’t referencing ‘nothing’,
butpossibly every user in the domain (we won’t know, since NULL is ‘unknown’ after all and that could match anything). 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

A supporting view works OK.
-------------------------
CREATE VIEW api2users AS SELECT * from mailusers;
CREATE or replace RULE remove_user AS ON DELETE TO api2users do instead
(update mailboxes set username=null where username=old.username and
domain=old.domain; delete from mailusers where username=old.username and
domain=old.domain);

DELETE FROM api2users where username='postmaster'  and domain='example.com';
DELETE 1
-------------------------

So it probably stays ... although I'm not particularly happy with that;
Still, the problem at hand is solved.


W dniu 03.01.2015 o 19:04, Alban Hertroys pisze:
[-------------------]
> Well, that’s embarrassing, it’s only a few weeks since I learned this and I’m already attributing the theory to the
wrongdatabase deity! That’s Codd-relationality, of course. Not Boyce. 
>
>

The theory got me intrigued. google
(http://en.wikipedia.org/wiki/Codd%27s_12_rules) says:
rule 3: "systematic treatment of null values"; hmmm.... this is a little
broader then "support for null". I would think, that:
1. if a sequence of "update XX set fk_field=null; then delete YY
depending on that FK", for a particular schema definition works ...
2. so the implementation of FK should support that too ... to be called
"systematic", right?
3. and the simplest way to do that for the case at hand, within an "on
delete action", is to skip those parts of FK, that are marked as "not
null" within the referring table. That would be a "requirement" for
rdbms implementation that claims compliance with Codd rule nr.3 :)

I think :)

-R

> On 03 Jan 2015, at 23:14, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
>> Well, that’s embarrassing, it’s only a few weeks since I learned this and I’m already attributing the theory to the
wrongdatabase deity! That’s Codd-relationality, of course. Not Boyce. 
>
> The theory got me intrigued. google (http://en.wikipedia.org/wiki/Codd%27s_12_rules) says:
> rule 3: "systematic treatment of null values"; hmmm.... this is a little broader then "support for null". I would
think,that: 
> 1. if a sequence of "update XX set fk_field=null; then delete YY depending on that FK", for a particular schema
definitionworks ... 
> 2. so the implementation of FK should support that too ... to be called "systematic", right?
> 3. and the simplest way to do that for the case at hand, within an "on delete action", is to skip those parts of FK,
thatare marked as "not null" within the referring table. That would be a "requirement" for rdbms implementation that
claimscompliance with Codd rule nr.3 :) 

I translated Codd-relationality to English, possibly it’s named differently.

Oddly enough, I can’t find any reference to Codd being responsible for this rule anywhere on the internet. What I did
findis that the theory I referred to stems from 1970(!), but that’s the closest I got. 
Anyway, wikipedia has the following to say about the issue, although their explanation is a bit obtuse concerning the
definitionof candidate keys from which to choose a primary key (http://en.wikipedia.org/wiki/Unique_key): 

"A table can have at most one primary key, but it may have more than one candidate key. A primary key is a combination
ofcolumns which uniquely specify a row; it is a special case of unique keys. One difference is that primary keys have
animplicit NOT NULL constraint while unique keys do not." 

They blatantly leave out why primary keys have that implicit NOT NULL! Is this some forgotten about piece of database
theory?I’ll have to ask my teachers where they got their definition from! 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

W dniu 04.01.2015 o 02:02, Alban Hertroys pisze:
[------------------------------]
>
> "A table can have at most one primary key, but it may have more than one candidate key. A primary key is a
combinationof columns which uniquely specify a row; it is a special case of unique keys. One difference is that primary
keyshave an implicit NOT NULL constraint while unique keys do not." 
>
> They blatantly leave out why primary keys have that implicit NOT NULL! Is this some forgotten about piece of database
theory?I’ll have to ask my teachers where they got their definition from! 
>

I'd say, that this is because it's a definition. "primary key" is
defined that way: they,ve chosen a name ("primary") and features (a
unique key over not null attributes, and no other key using a name of
"primary"), and that's it. And the goal for such definition was possibly
also quite trivial: it's for the programmers like ourselves to
communicate efficiently what we mean.

I'm still not convinced of how the "norm/specs/theory" expect engine to
treat inconsistent constraint setup. In this case, the setup is
inconsistent: "on delete set null" contradicts "not null" attribute. So:
should the engine rise an error (like it does), or should it just humbly
comply with the setup, and only do what's allowed by the setup, i.e.:
skip the "not null" columns when executing "on delete" action.

-R

>> The theory got me intrigued. google (http://en.wikipedia.org/wiki/Codd%27s_12_rules) says:
>> rule 3: "systematic treatment of null values"; hmmm.... this is a little broader then "support for null". I would
think,that: 
>> 1. if a sequence of "update XX set fk_field=null; then delete YY depending on that FK", for a particular schema
definitionworks ... 
>> 2. so the implementation of FK should support that too ... to be called "systematic", right?
>> 3. and the simplest way to do that for the case at hand, within an "on delete action", is to skip those parts of FK,
thatare marked as "not null" within the referring table. That would be a "requirement" for rdbms implementation that
claimscompliance with Codd rule nr.3 :) 
>
> I translated Codd-relationality to English, possibly it’s named differently.
>
> Oddly enough, I can’t find any reference to Codd being responsible for this rule anywhere on the internet. What I did
findis that the theory I referred to stems from 1970(!), but that’s the closest I got. 

My teachers got back to me; a while ago already, to be fair. They were as surprised that this can't be found on the
internetas I was, but it should be possible to find it in printed form. I'd suggest a university library or a good
technicalbook store. 

Apparently, this particular theory is explained in E.F. Codd: "The relational model for database management". There are
probablyother books that do too. 

Unfortunately, I do not own a copy so I can't verify. If anyone who does own a copy could confirm or even quote the
relevantsection, that would be great. Better yet, perhaps this should find it's way (back) to the internet? I'm still
muchsurprised that Wikipedia didn't have this. 

Oh, and perhaps we could get a reference in the PG docs on primary keys and NULLs to the theory? Do we have such things
inthe docs? It sounds like a good idea to me, I always find it helpful to know why things are how they are. But I don't
writethe docs so this is just one for the ideas-box. 

Cheers,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

On Sun, 2015-01-25 at 14:09 +0100, Alban Hertroys wrote:
> >> The theory got me intrigued. google (http://en.wikipedia.org/wiki/Codd%27s_12_rules) says:
> >> rule 3: "systematic treatment of null values"; hmmm.... this is a little broader then "support for null". I would
think,that: 
> >> 1. if a sequence of "update XX set fk_field=null; then delete YY depending on that FK", for a particular schema
definitionworks ... 
> >> 2. so the implementation of FK should support that too ... to be called "systematic", right?
> >> 3. and the simplest way to do that for the case at hand, within an "on delete action", is to skip those parts of
FK,that are marked as "not null" within the referring table. That would be a "requirement" for rdbms implementation
thatclaims compliance with Codd rule nr.3 :) 
> >
> > I translated Codd-relationality to English, possibly it’s named differently.
> >
> > Oddly enough, I can’t find any reference to Codd being responsible for this rule anywhere on the internet. What I
didfind is that the theory I referred to stems from 1970(!), but that’s the closest I got. 
>
> My teachers got back to me; a while ago already, to be fair. They were as surprised that this can't be found on the
internetas I was, but it should be possible to find it in printed form. I'd suggest a university library or a good
technicalbook store. 
>
> Apparently, this particular theory is explained in E.F. Codd: "The relational model for database management". There
areprobably other books that do too. 
>
> Unfortunately, I do not own a copy so I can't verify. If anyone who does own a copy could confirm or even quote the
relevantsection, that would be great. Better yet, perhaps this should find it's way (back) to the internet? I'm still
muchsurprised that Wikipedia didn't have this. 
>
> Oh, and perhaps we could get a reference in the PG docs on primary keys and NULLs to the theory? Do we have such
thingsin the docs? It sounds like a good idea to me, I always find it helpful to know why things are how they are. But
Idon't write the docs so this is just one for the ideas-box. 
>
> Cheers,
>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>
>


The following link

http://www.databaseanswers.org/codds_rules.htm

sets out Ted Codd's rules according to C.J. Date.

I don't have a copy of the book so I can't verify the accuracy of what
is published at this link.

> On Sun, 2015-01-25 at 14:09 +0100, Alban Hertroys wrote:
>
> The following link
>
> http://www.databaseanswers.org/codds_rules.htm
>
> sets out Ted Codd's rules according to C.J. Date.

As you might have noticed, those were referred to already upthread, but that is a while ago now.
Although those rules are certainly Codd's, that's an entirely different bit of database theory. It's what I did manage
tofind while searching the Internet for Codd relationality. 

What I understand of them, these 12 rules determine whether a DBMS can be called relational, whereas Codd-relationality
isonly applicable in DBMS's that already qualify as relational and restricts primary keys to non-nullable fields. 

I know it's hard to believe that the Internet does not contain some information, certainly when the information is a
rulethat is applied and documented in pretty much every relational database in existence (but without mentioning it by
nameor why it exists). I'm afraid that might be the case here though. Possibly this could happen because the theory in
questionpredates the internet. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

> 1. I have a table with mailmessages, which has an FK to a table of hub users.
> 2. I'd like to retain the content of message repository (with it's domain key not cleared), when I drop a particular
usernamefrom service .... to release that username to others. 
> 3. I try to do that with FK "on-update/on-delete" actions, but to no avail:

Interesting discussion on database theory!

If you switched to surrogate keys for all your tables, you could
soft-delete accounts with a deleted_at column, and then have a unique
index on username+domain (or username+domain_id?) that is WHERE
deleted_at IS NULL. Does that work? Probably you want the same
approach for the maildomains table to enforce unique non-deleted
domains.

It seems like if you want to retain data for auditing, you don't
really want to delete *anything*, including the username. Surrogate
keys and a partial unique index would let you do that I believe.

Paul

--
_________________________________
Pulchritudo splendor veritatis.

Alban Hertroys <haramrae@gmail.com> wrote:

> Unfortunately, I do not own a copy so I can't verify.  If anyone
> who does own a copy could confirm or even quote the relevant
> section, that would be great.

I have a copy of The RELATIONAL MODEL for DATABASE MANAGEMENT,
VERSION 2 (RM/V2), by E. F. Codd, Copyright 1990, on my desk, but I
wasn't entirely clear on what you were looking for a quote about.
In the preface he says that from 1968 to 1988 he published more
than 30 technical papers on the relational model, which he
collectively refers to in this book as RM/V1.

If you were looking for his views on NULL, I can tell you that in
1990 he preferred to refer to "marks" to indicate missing
information, and just the index entries on the topic would be too
big to quote here (taking nearly an entire page).  The Missing
Information chapter is 27 pages long.  The Response to Technical
Criticisms Regarding Missing Information is another 10 pages.
Also, these chapters refer to separate discussions of particular
issues related to missing values in other chapters.

He mentions that RM/V1 only had one type of mark for missing data
which was referred to in the earlier work as a *null* or *null
value*, so the term may have originated with him (I don't have
copies of all the relevant papers); but in RM/V2 he argues that the
difference between a value which is missing-but-applicable (just
currently unknown) is different enough from the case where a value
would be inapplicable (i.e., the value is unknowable) that there
should be separate marks for them, which he dubbed the A-mark and
I-mark, respectively.

I'm not aware of any product which has implementing the separate
types of marks for missing data, but I agree with his arguments
that while NULL is far superior to "magic values", the NULL concept
lacks enough semantic depth to avoid confusion.  If I were
developing a database from scratch today I would try very hard to
implement his ideas regarding data marked as missing, but it's hard
to see how to retro-fit it into a stable product.  :-(

If you have a question about a specific area of how missing values
should be handled according to RM/V2, please respond with a
question about it that is narrow enough to deal with in an email.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 1/3/15 2:49 AM, Rafal Pietrak wrote:
>
> ---------test schema-----------------------
> CREATE TABLE maildomains (domain text primary key, profile text not null);
> CREATE  TABLE mailusers (username text , domain text references
> maildomains(domain) on update cascade, primary key (username, domain));
> CREATE  TABLE mailboxes (username text, domain text not null,
> mailmessage text not null , foreign key (username, domain) references
> mailusers (username,domain) on update cascade);
> --------------------------------
>
> ----------test data-------------
> INSERT  INTO  maildomains (domain, profile ) VALUES ('example.com',
> 'active');
> INSERT  INTO  mailusers (username,domain) VALUES ('postmaster',
> 'example.com');
> INSERT  INTO  mailboxes (username,domain, mailmessage) VALUES
> ('postmaster', 'example.com', 'Hello');
> ----------------------------------
>
> -------------the goal functionality ...  doesnt work at the
> moment--------------------
> DELETE FROM mailusers ;
> ERROR:  update or delete on table "mailusers" violates foreign key
> constraint "mailboxes_username_fkey" on table "mailboxes"
> details:  Key (username, domain)=(postmaster, example.com) is still
> referenced from table "mailboxes".
> --------------------------------------------------------

Well, you didn't specify ON DELETE SET NULL, but that wouldn't work
anyway because it'd attempt to set both username *and* domain to NULL.
Note also that for this to work you'd probably need to specify  MATCH
SIMPLE.

> But an application could do
> ---------a successfull scenario with expected result-------------------
> testvm=# UPDATE  mailboxes SET username = null;
> UPDATE 1
> testvm=# DELETE FROM mailusers ;
> DELETE 1
> -----------------------------------------------------------
> Which works just fine.
>
> So I add a TRIGER BEFORE, to have the above first statement get executed
> just like in the above example: before the actual DELETE:
> ----------------------
> 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();
> -----------------------------

That trigger function is NOT doing the same thing as above. What you
want is something that does UPDATE mailboxes SET username = null WHERE
username = OLD.username. You'd need to make sure that trigger ran before
the RI trigger did.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

W dniu 03.02.2015 o 04:44, Jim Nasby pisze:
> On 1/3/15 2:49 AM, Rafal Pietrak wrote:
>>
[-----------------------]
>> But an application could do
>> ---------a successfull scenario with expected result-------------------
>> testvm=# UPDATE  mailboxes SET username = null;
>> UPDATE 1
>> testvm=# DELETE FROM mailusers ;
>> DELETE 1
>> -----------------------------------------------------------
>> Which works just fine.
>>
>> So I add a TRIGER BEFORE, to have the above first statement get executed
>> just like in the above example: before the actual DELETE:
>> ----------------------
>> 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();
>> -----------------------------
>
> That trigger function is NOT doing the same thing as above. What you
> want is something that does UPDATE mailboxes SET username = null WHERE
> username = OLD.username. You'd need to make sure that trigger ran
> before the RI trigger did.

Oh. yes. It actually does work. Silly me making such obvious mistakes.

Nonetheless I stand my grounds on the FK internal functionality opinion,
in that if FK constraint internal triggers would SET NULL (when cascaded
set null is declared) only those columns of the target, that are
actually NULL-able, then the coding (SQL application programmer's life
like myown) would get easier in such usage scenarios.

thenx anyway!

-R