Re: partial "on-delete set null" constraint

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