Postgres Pro
Downloads
Home   >   mailing lists

Thread: possible to DELETE CASCADE?

possible to DELETE CASCADE?

From
Miles Keaton
Date:
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?

Re: possible to DELETE CASCADE?

From
Thomas Braad Toft
Date:
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)

You just have to use ON DELETE CASCADE on your foreign key definition in
all the table which reference the client.

See http://www.postgresql.org/docs/7.4/interactive/sql-createtable.html
and look for FOREIGN KEY and ON DELETE CASCADE. That will have the
effect you are looking for. If this is not enough you will have to
create a trigger for the scenario.

--
Thomas Braad Toft

Re: possible to DELETE CASCADE?

From
Miles Keaton
Date:
On Thu, 30 Dec 2004 11:10:38 -0800, I wrote:
> Is it possible for a query to delete a record and all of its
> foreign-key dependents?


Sorry - to be more clear : I like having my foreign keys RESTRICT from
this kind of cascading happening automatically or accidently.

So I'm looking for a query that could force it to happen, if truly intended.

Re: possible to DELETE CASCADE?

From
Robby Russell
Date:
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 ---
****************************************/

Re: possible to DELETE CASCADE?

From
Bruno Wolff III
Date:
On Thu, Dec 30, 2004 at 11:40:21 -0800,
  Miles Keaton <mileskeaton@gmail.com> wrote:
> On Thu, 30 Dec 2004 11:10:38 -0800, I wrote:
> > Is it possible for a query to delete a record and all of its
> > foreign-key dependents?
>
>
> Sorry - to be more clear : I like having my foreign keys RESTRICT from
> this kind of cascading happening automatically or accidently.
>
> So I'm looking for a query that could force it to happen, if truly intended.


Patient: Doctor, it hurts when I do this.
Doctor: Then stop doing that.

Maybe you should only allow a special account to be able to delete from
the parent table and control access to that special account. Depending
on what kind of accidents you are trying to prevent, this may help.

Re: possible to DELETE CASCADE?

From
Miles Keaton
Date:
Cool.  Thanks for all the advice, guys.

I'll just keep my script manually deleting dependencies, then.  It
gives me peace of mind.

:-)