Professional Web Applications Themes

possible to DELETE CASCADE? - PostgreSQL / PGSQL

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? ...

  1. #1

    Default possible to DELETE CASCADE?

    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?

    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Miles Keaton Guest

  2. #2

    Default Re: possible to DELETE CASCADE?

    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 [url]http://www.postgresql.org/docs/7.4/interactive/sql-createtable.html[/url]
    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

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Thomas Braad Toft Guest

  3. #3

    Default Re: possible to DELETE CASCADE?

    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.

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Miles Keaton Guest

  4. #4

    Default 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 | [url]www.planetargon.com[/url]
    * Portland, OR | [email]robbyplanetargon.com[/email]
    * 503.351.4730 | blog.planetargon.com
    * PHP/PostgreSQL Hosting & Development
    * --- Now supporting PHP5 ---
    ****************************************/



    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Robby Russell Guest

  5. #5

    Default Re: possible to DELETE CASCADE?

    On Thu, Dec 30, 2004 at 11:40:21 -0800,
    Miles Keaton <mileskeaton> 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.

    ---------------------------(end of broadcast)---------------------------
    TIP 7: don't forget to increase your free space map settings

    Bruno Wolff III Guest

  6. #6

    Default Re: possible to DELETE CASCADE?

    Cool. Thanks for all the advice, guys.

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

    :-)

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Miles Keaton Guest

Similar Threads

  1. Dropdownlist Cascade
    By JimmyB4B in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: December 18th, 04:50 AM
  2. A "cascade on delete" constraints deletes AFTER the source is gone??
    By Vitaly Belman in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: December 20th, 05:36 PM
  3. Perform cascade delete
    By basidati in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 7th, 09:53 PM
  4. cascade update non-primary key field
    By Calvin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 2nd, 01:37 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139