Re: Deletion Recursively
От | Greg Sabino Mullane |
---|---|
Тема | Re: Deletion Recursively |
Дата | |
Msg-id | 200110292049.PAA23781@smtp6.mindspring.com обсуждение исходный текст |
Ответ на | Deletion Recursively (Michael Dyrby Jensen <dyrby@nork.auc.dk>) |
Список | 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, CONSTRAINThas_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 | AfricaParis | France | EuropeBrie | France | EuropeLisbon | Spain | EuropeAquala | Xanadu | AtlantisAragornia | 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 | AfricaParis | France | EuropeBrie | France | EuropeLisbon | 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-----
В списке pgsql-sql по дате отправления: