SQL 'DELETE CASCADE'

Question

I have a table B that has a foreign key to table A, and now I want to do a sorta "DELETE CASCADE" thingie on A, but PostgreSQL won't accept the following:

DELETE FROM ATable WHERE aid IN
(
    DELETE FROM BTable WHERE aid IN
    (
        ... [expression that depends on the entries in BTAble] ...
    )
    RETURNING aid
);

Seems that only SELECT can be inside the IN () clause. I suppose there is some easy (and standard SQL, not PostgreSQL-specific?) way of doing this?

Edit: is it safe to say that something is badly structured when you bump into this kind of problem? In our case I have a gut feeling that the hits in ..[expr].. should be in a new CTAble instead of as a subset in ATable, but I can't really point to any design paradigm to support that.

score 2 · Answer 1 · answered Apr 15 '11 at 12:20

2

This will be possible with PostgreSQL 9.1, but I don't think there is any way doing that without defining cascading constraints.

http://developer.postgresql.org/pgdocs/postgres/queries-with.html#QUERIES-WITH-MODIFYING

answered Apr 15 '11 at 12:20

1

Ouch! Are you talking about something along the lines of 'ON UPDATE CASCADE ON DELETE CASCADE' on the foreign key? developer.postgresql.org currently not working for me... – Jonas Byström Apr 15 '11 at 12:28
@Jonas: yes the `ON DELETE CASCADE` is s what I'm talking about – Apr 15 '11 at 12:31

score 2 · Answer 2 · answered Apr 15 '11 at 12:30

You can wait for 9.1 or create a set-returning function meanwhile:

CREATE OR REPLACE FUNCTION fn_delete_btable(params)
RETURNS SETOF btable.aid%TYPE
AS
$$
        DELETE
        FROM    btable
        WHERE   expression_over_params(params)
        RETURNING 
                aid
$$
LANGUAGE 'sql';

DELETE
FROM    atable
WHERE   aid IN
        (
        SELECT  aid
        FROM    fn_delete_btable(params)
        )

P.S. Just in case if you're not aware about standard SQL way of doing it.

If you create the tables like this:

CREATE TABLE btable (aid INT NOT NULL UNIQUE, …)
CREATE TABLE atable (aid INT NOT NULL FOREIGN KEY REFERENCES (aid) ON DELETE CASCADE, …)

then a delete from btable will trigger a delete from atable as well.

For this to work, btable.aid should be UNIQUE or a PRIMARY KEY, and this is less efficient for mass updates than a set-based solution.

Nice idea about the function returning the ids – Apr 15 '11 at 12:31 — , Apr 15 '11 at 12:31

score 1 · Answer 3 · edited Apr 15 '11 at 12:32

1

You should be able to do that: here is an example that I found on the bottom of this page.

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

edited Apr 15 '11 at 12:32

answered Apr 15 '11 at 12:29

John Kane

4,383
1
24
42

score -1 · Accepted Answer · answered Apr 15 '11 at 12:48

-1

DB functions are outside my comfort zone (I know, I know) and I didn't want to make even temporary changes to the columns in question so I simply did a

CREATE TABLE CTable AS ... [expression that depends on BTAble] ...;

and used that to sequentially delete data in B and A.

answered Apr 15 '11 at 12:48

Jonas Byström

25,316
23
100
147

You're probably right in the general case. For me this was the simplest solution to my problem. – Jonas Byström Apr 20 '11 at 08:22

SQL 'DELETE CASCADE'

4 Answers4