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