Skip site navigation (1) Skip section navigation (2)

Re: possible to DELETE CASCADE?

From: Robby Russell <robby(at)planetargon(dot)com>
To: Miles Keaton <mileskeaton(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: possible to DELETE CASCADE?
Date: 2004-12-30 19:54:35
Message-ID: 1104436476.26809.143.camel@linus (view raw or flat)
Thread:
Lists: 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(at)planetargon(dot)com
* 503.351.4730  | blog.planetargon.com
* PHP/PostgreSQL Hosting & Development
*    --- Now supporting PHP5 ---
****************************************/



In response to

pgsql-general by date

Next:From: Timothy PerrigoDate: 2004-12-30 20:32:05
Subject: syntax for inserting unicode character literal
Previous:From: Miles KeatonDate: 2004-12-30 19:40:21
Subject: Re: possible to DELETE CASCADE?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group