[Gnumed-devel] Postgres method / expression help to delete related records

[Jim Busser]

I wanted to delete all existing brands but – on my first effort 
        gnumed_v16=> delete from ref.branded_drug;

I was told by psql

        ERROR:  update or delete on table "branded_drug" violates foreign
                key constraint "vaccine_fk_brand_fkey" on table "vaccine"
        DETAIL:  Key (pk)=(44) is still referenced from table "vaccine".


So I thought "well then, let me keep the vaccines"

        gnumed_v16=> delete from ref.branded_drug where not preparation = 
'vaccine' ;

and I got

        ERROR:  update or delete on table "branded_drug" violates foreign
                key constraint "lnk_substance2brand_fk_brand_fkey" on table 
"lnk_substance2brand"
        DETAIL:  Key (pk)=(1) is still referenced from table 
"lnk_substance2brand".


Then I thought -- having already deleted all the rows in

        clin.substance_intake

since they were demo records, I could delete the rows in the link table, but on 
attempting

        gnumed_v16=> delete from ref.lnk_substance2brand ;

I got

        ERROR:  [ref.trf_true_brands_must_have_components::DELETE] brand must 
have components (brand <78> component <8>)


so I gather I must the link rows can only be deleted in the same transaction as 
would delete the referenced brands rows.


But how do I delete from two tables, when the scope of the delete command is a 
single table?


-- Jim