Re: partial "on-delete set null" constraint

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(dot)klaver(at)aklaver(dot)com