Re: possible to DELETE CASCADE?

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