Re: possible to DELETE CASCADE?
От | Robby Russell |
---|---|
Тема | Re: possible to DELETE CASCADE? |
Дата | |
Msg-id | 1104436476.26809.143.camel@linus обсуждение исходный текст |
Ответ на | possible to DELETE CASCADE? (Miles Keaton <mileskeaton@gmail.com>) |
Список | pgsql-general |
On Thu, 2004-12-30 at 11:10 -0800, Miles Keaton wrote: > Is it possible for a query to delete a record and all of its > foreign-key dependents? > > I see DROP CASCADE, but not a DELETE CASCADE. > > What I'm trying to do: > I have a "clients" table. > I have many different tables that use the clients.id as a foreign key. > When I delete a client, I want it to delete all records in those many > different tables that reference this client. > > Right now I have my script passing many queries to delete them > individually. ("delete from history where client_id=?; delete from > payments where client_id=?" -- etc) > > Any shortcut way to do this? You can use ON DELETE CASCADE when you create/alter the table. for example: CREATE TABLE foo_type ( id SERIAL PRIMARY KEY NOT NULL, name TEXT ); INSERT INTO foo_type(name) VALUES ('type 1'); INSERT INTO foo_type(name) VALUES ('type 2'); CREATE TABLE foo ( id SERIAL PRIMARY KEY NOT NULL, foo_type_id INT REFERENCES foo_type ON DELETE CASCADE, name TEXT ); INSERT INTO foo (foo_type_id, name) VALUES (1, 'bar'); INSERT INTO foo (foo_type_id, name) VALUES (1, 'bar2'); INSERT INTO foo (foo_type_id, name) VALUES (2, 'bar3'); > test=> SELECT * FROM foo; > id | foo_type_id | name > ----+-------------+------ > 1 | 1 | bar > 2 | 1 | bar2 > 3 | 2 | bar3 > (3 rows) > > test=> SELECT * FROM foo_type; > id | name > ----+-------- > 1 | type 1 > 2 | type 2 > (2 rows) Now, I will test it: test=> DELETE FROM foo_type WHERE id = 1; DELETE 1 test=> SELECT * FROM foo; id | foo_type_id | name ----+-------------+------ 3 | 2 | bar3 (1 row) -- /*************************************** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | robby@planetargon.com * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting & Development * --- Now supporting PHP5 --- ****************************************/
В списке pgsql-general по дате отправления: