From dyrby@nork.auc.dk Fri Oct 26 19:53:09 2001 Received: from aua2.aua.auc.dk (aua2.aua.auc.dk [130.225.54.5]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9QJr9P97329 for ; Fri, 26 Oct 2001 15:53:09 -0400 (EDT) (envelope-from dyrby@nork.auc.dk) Received: from nork.auc.dk (carlsberg.nork.auc.dk [192.38.54.51]) by aua.auc.dk (PMDF V6.0-24 #38884) with SMTP id <01K9Z09ECV8A8WWUWI@aua.auc.dk> for pgsql-sql@postgresql.org; Fri, 26 Oct 2001 21:53:06 +0200 (MET-DST) Received: (qmail 3448 invoked from network); Fri, 26 Oct 2001 19:54:11 +0000 Received: from chilibeer.nork.auc.dk (HELO chilibeer) (192.38.54.145) by carlsberg.nork.auc.dk with SMTP; Fri, 26 Oct 2001 19:54:11 +0000 Date: Fri, 26 Oct 2001 21:55:53 +0200 From: Michael Dyrby Jensen Subject: Deletion Recursively To: pgsql-sql@postgresql.org Message-id: <000a01c15e58$3868a680$6f6f6f6f@nork.auc.dk> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Mailer: Microsoft Outlook Express 5.50.4522.1200 Content-type: multipart/alternative; boundary="----=_NextPart_000_0007_01C15E68.FBC843A0" X-Priority: 3 X-MSMail-priority: Normal X-Archive-Number: 200110/389 X-Sequence-Number: 5341 This is a multi-part message in MIME format. ------=_NextPart_000_0007_01C15E68.FBC843A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 power= ful 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 campaig= ns have references to the banners that they own. My question is then: "How do I make the Database delete the campaigns and b= anners linked to the advertiser, when I delete him?" A small example would really really help me alot. (with small explanation too ofcause =3D) ) Yours, Michael. ------=_NextPart_000_0007_01C15E68.FBC843A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Greetings.
 
I am fairly new with the postgresql=20 database.
And I have run into a problem, which I hop= e someone=20 can help me with.
 
I am doing a school project, where I have = chosen to=20 learn to use this powerful tool, so I am using it to
handle a fictive company of banner=20 advertising.
 
I need to register Advertisers, who got so= me=20 Campaigns, and these campaigns have banners.
I have an Advertiser table, a campaign tab= le and a=20 banner table.
 
an advertiser hold references to the campa= igns that=20 he own, and the campaigns have references to the banners that they=20 own.
 
My question is then: "How do I make the Da= tabase=20 delete the campaigns and banners linked to the advertiser, when I delete=20 him?"
 
A small example would really really help m= e=20 alot.
(with small explanation too ofcause =3D)= =20 )
 
Yours, Michael.
 
------=_NextPart_000_0007_01C15E68.FBC843A0-- From sszabo@megazone23.bigpanda.com Mon Oct 29 17:51:21 2001 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, ... ); From greg@turnstep.com Mon Oct 29 20:49:43 2001 Received: from smtp6.mindspring.com (smtp6.mindspring.com [207.69.200.110]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9TKnhP72959 for ; Mon, 29 Oct 2001 15:49:43 -0500 (EST) (envelope-from greg@turnstep.com) Received: from dialup.mindspring.com (user-2inib15.dsl.mindspring.com [165.121.44.37]) by smtp6.mindspring.com (8.9.3/8.8.5) with SMTP id PAA23781; Mon, 29 Oct 2001 15:49:40 -0500 (EST) Date: Mon, 29 Oct 2001 15:49:40 -0500 (EST) Message-Id: <200110292049.PAA23781@smtp6.mindspring.com> To: From: "Greg Sabino Mullane" Subject: Re: Deletion Recursively X-Echelon: Treasury cryptographic Peking Ft. Meade Legion of Doom KGB terrorist terrorist X-PGP-Fingerprint: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 X-Mailer: GregMail 1.21 X-Archive-Number: 200110/401 X-Sequence-Number: 5353 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 What you need are foreign keys with a cascade delete. Here is a quick example: CREATE TABLE continent ( continent_id INTEGER NOT NULL PRIMARY KEY, continent_name VARCHAR (20) ); CREATE TABLE country ( country_id INTEGER NOT NULL PRIMARY KEY, country_name VARCHAR(40), continent_id INTEGER, CONSTRAINT has_continent FOREIGN KEY(continent_id) REFERENCES continent ON DELETE CASCADE ); CREATE TABLE city ( city_id INTEGER NOT NULL PRIMARY KEY, city_name VARCHAR(100), country_id INTEGER, CONSTRAINT has_country FOREIGN KEY(country_id) REFERENCES country ON DELETE CASCADE ); Now we add some continents: INSERT INTO continent(continent_id, continent_name) VALUES (1, 'Africa'); INSERT INTO continent(continent_id, continent_name) VALUES (2, 'Europe'); INSERT INTO continent(continent_id, continent_name) VALUES (3, 'Atlantis'); Then some countries: INSERT INTO country(country_id, country_name, continent_id) VALUES (1,'France',2); INSERT INTO country(country_id, country_name, continent_id) VALUES (2,'Xanadu',3); INSERT INTO country(country_id, country_name, continent_id) VALUES (3,'Spain',2); INSERT INTO country(country_id, country_name, continent_id) VALUES (4,'Kenya',1); INSERT INTO country(country_id, country_name, continent_id) VALUES (5,'Ethiopia',1); INSERT INTO country(country_id, country_name, continent_id) VALUES (6,'Fantasia',3); And finally a few cities: INSERT INTO city(city_id, city_name, country_id) VALUES(1, 'Paris', 1); INSERT INTO city(city_id, city_name, country_id) VALUES(2, 'Lisbon', 3); INSERT INTO city(city_id, city_name, country_id) VALUES(3, 'Aquala', 2); INSERT INTO city(city_id, city_name, country_id) VALUES(4, 'Mombasa', 4); INSERT INTO city(city_id, city_name, country_id) VALUES(5, 'Aragornia', 6); INSERT INTO city(city_id, city_name, country_id) VALUES(6, 'Brie', 1); Let's take a look at what we have. We'll list all the cities that are in the 'city' table, and also show which country and continent they belong to: SELECT city_name, country_name, continent_name FROM city C1, country C2, continent C3 WHERE C1.country_id=C2.country_id AND C2.continent_id = C3.continent_id; city_name | country_name | continent_name - -----------+--------------+---------------- Mombasa | Kenya | Africa Paris | France | Europe Brie | France | Europe Lisbon | Spain | Europe Aquala | Xanadu | Atlantis Aragornia | Fantasia | Atlantis (6 rows) As you can see, there are 6 rows, for 6 cities. Let's get rid of Atlantis, since it does not really exists. When we do this, it also gets rid of any countries within Atlantis, as well as any cities within those countries. The deletes cascade down from the original level, down to the countries, and then down to the cities: DELETE FROM continent WHERE continent_name='Atlantis'; Now let's take a look using the same SELECT statement: city_name | country_name | continent_name - -----------+--------------+---------------- Mombasa | Kenya | Africa Paris | France | Europe Brie | France | Europe Lisbon | Spain | Europe (4 rows) Only 4 rows! Not only did we delete rows from the continent table, but also from the country and the city tables as well. This was a crude example: for example, the primary keys used should really be sequences, so that we don't have to worry about keeping track of them ourselves. But hopefully it illustrates foreign keys and a cascade delete. Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200110291549 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iQA/AwUBO93BUrybkGcUlkrIEQJALACdHK71UjKfUOvUook9a01PRlRWp94AoNXk oFYHj6zvZ6DIIYHQFgc7TQcL =KN96 -----END PGP SIGNATURE-----