pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDeletion Recursively
3+ messages / 3 participants
[nested] [flat]
* Deletion Recursively
@ 2001-10-26 19:55 Michael Dyrby Jensen <dyrby@nork.auc.dk>
2001-10-29 17:51 ` Re: Deletion Recursively Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Dyrby Jensen @ 2001-10-26 19:55 UTC (permalink / raw)
To: pgsql-sql
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 =) )
Yours, Michael.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Deletion Recursively
2001-10-26 19:55 Deletion Recursively Michael Dyrby Jensen <dyrby@nork.auc.dk>
@ 2001-10-29 17:51 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2001-10-29 17:51 UTC (permalink / raw)
To: Michael Dyrby Jensen <dyrby@nork.auc.dk>; +Cc: pgsql-sql
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,
...
);
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Deletion Recursively
@ 2001-10-29 20:49 Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 0 replies; 3+ messages in thread
From: Greg Sabino Mullane @ 2001-10-29 20:49 UTC (permalink / raw)
To: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-10-29 20:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-26 19:55 Deletion Recursively Michael Dyrby Jensen <dyrby@nork.auc.dk>
2001-10-29 17:51 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-29 20:49 Re: Deletion Recursively Greg Sabino Mullane <greg@turnstep.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox