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(at)planetargon(dot)com
* 503.351.4730 | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
* --- Now supporting PHP5 ---
****************************************/