pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Subject: Re: Deletion Recursively
Date: Mon, 29 Oct 2001 15:49:40 -0500 (EST)
Message-ID: <200110292049.PAA23781@smtp6.mindspring.com> (raw)


-----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-----




view thread (3+ messages)

Message-ID: <200110292049.PAA23781@smtp6.mindspring.com>
Permalink:  ../200110292049.PAA23781@smtp6.mindspring.com/
Also on:    postgresql.org/message-id/200110292049.PAA23781@smtp6.mindspring.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: greg@turnstep.com
  Subject: Re: Deletion Recursively
  In-Reply-To: <200110292049.PAA23781@smtp6.mindspring.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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