Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9THpKP22479 for ; Mon, 29 Oct 2001 12:51:21 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id EFDABD5CB; Mon, 29 Oct 2001 09:51:19 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id E37845BD0; Mon, 29 Oct 2001 09:51:19 -0800 (PST) Date: Mon, 29 Oct 2001 09:51:19 -0800 (PST) From: Stephan Szabo To: Michael Dyrby Jensen Cc: Subject: Re: Deletion Recursively In-Reply-To: <000a01c15e58$3868a680$6f6f6f6f@nork.auc.dk> Message-ID: <20011029094657.P9662-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200110/395 X-Sequence-Number: 5347 On Fri, 26 Oct 2001, Michael Dyrby Jensen wrote: > Greetings. > > I am fairly new with the postgresql database. > And I have run into a problem, which I hope someone can help me with. > > I am doing a school project, where I have chosen to learn to use this powerful tool, so I am using it to > handle a fictive company of banner advertising. > > I need to register Advertisers, who got some Campaigns, and these > campaigns have banners. > I have an Advertiser table, a campaign table and a banner table. > > an advertiser hold references to the campaigns that he own, and the > campaigns have references to the banners that they own. > > My question is then: "How do I make the Database delete the campaigns > and banners linked to the advertiser, when I delete him?" > > A small example would really really help me alot. > (with small explanation too ofcause =) ) If I'm understanding your problem correctly... (untested, but I think I got the syntax stuff right) create table advertiser ( advid serial primary key, name varchar, ... ); create table campaigns ( campid serial primary key, advid integer references advertisers(advid) on delete cascade on update cascade, -- Make a foreign key constraint to the advertiser -- table such that a campaign must either have a -- NULL advid or a valid one at all times. If the -- advid is updated in advertiser, all of the ones -- in campaign that references that one are updated -- (on update cascade). If a row is deleted in -- advertiser, all rows in campaign that reference -- that one are deleted (on delete cascade) ... ); create table banners ( bannerid serial, campid integer references campaigns(campid) on delete cascade on update cascade, ... );