CODEWITHSUNDEEP
sqlpostgresqltriggers
Ashwin
Ashwin

Reputation: 13537

Delete table rows depending upon rows deleted in another table

I have two tables t1 and t2. I am deleting certain rows of t2 this way:

delete from t2 where expiry < NOW();

There is a column id common to both t1 and t2. When the above statement is executed, I also want to delete corresponding rows in t1 if any (there might be none).

How can this be done?
Is it possible to write a single query for both these operations?

Upvotes: 1

Views: 4275

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

I see at least three good ways - depending on further details missing in your question. I build on your example:

  • t2 is the mother table
  • t1 the child

1. Foreign key constraint with ON CASCADE DELETE

Test bed:

CREATE TABLE t2 (
  id serial PRIMARY KEY  -- primary or unique key needed
, expiry timestamp
);

CREATE TABLE t1 (
  id int references t1(id) ON DELETE CASCADE  -- ON UPDATE CASCADE, too?
);

Populate tables:

INSERT INTO t2(expiry)
SELECT (now() + g * interval '1h')
FROM   generate_series(1, 10) g;   -- 10 arbitrary rows

INSERT INTO t1(id)
SELECT id FROM t2 WHERE id%2 = 0;  -- pick even numbers for t1

SELECT * FROM t1;

If you delete rows from t2, corresponding rows from t1 are deleted automatically:

DELETE FROM t2 WHERE id IN (1,2,3,4,5);
-- rows 2,4 in `t1` are deleted automatically

To add such a foreign key constraint to existing tables:

ALTER TABLE t1 ADD CONSTRAINT t1_id_fkey FOREIGN KEY (id)
REFERENCES t2 (id) ON DELETE CASCADE;

Of course, a foreign key requires a unique or primary key on t1.id. But you probably have that in a scenario like that.

2. Trigger AFTER DELETE

If the values in t2.id are not unique, or if you have values in t1.id violating a foreign key constraint, you could create a trigger AFTER DELETE instead.

Trigger function:

CREATE OR REPLACE FUNCTION t2_delaft
  RETURNS trigger 
  LANGUAGE plpgsql AS
$func$
BEGIN
   DELETE FROM t1
   WHERE  t1.id = OLD.id;

   RETURN NULL;  -- AFTER trigger can return NULL
END
$func$

Trigger:

CREATE TRIGGER delaft
AFTER DELETE ON t2
FOR EACH ROW EXECUTE PROCEDURE t2_delaft();

You could also implement a RULE. But I find triggers generally easier to handle.

3. Data modifying CTE

Forgive me for giving the simplest answer last. This works, no matter what - provided you are on PostgreSQL 9.1 or later:

WITH x AS (
    DELETE FROM t1
    WHERE  id IN (1,2,3,4,5)
    RETURNING id
    )
DELETE FROM t2
USING  x
WHERE  t2.id = x.id;

Upvotes: 2

Related Questions