Re: partial "on-delete set null" constraint
От | Jim Nasby |
---|---|
Тема | Re: partial "on-delete set null" constraint |
Дата | |
Msg-id | 54D0441C.4050205@BlueTreble.com обсуждение исходный текст |
Ответ на | Re: partial "on-delete set null" constraint (Rafal Pietrak <rafal@ztk-rp.eu>) |
Ответы |
Re: partial "on-delete set null" constraint
(Rafal Pietrak <rafal@ztk-rp.eu>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: