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