How to delete data from multiple tables?

sql postgresql foreign-keys sql-delete
user1202766 picture user1202766 · Apr 13, 2012 · Viewed 50.9k times · Source

I have these tables:

event     (evt_id, evt_code, reg_id)

magnitude (mag_id, evt_id, value)

trace     (trace_id, pt_id)

point     (pt_id, evt_id)

I want to delete all rows from all tables related to evt_id=1139.
How do I do it?

Answer

Justin Pihony picture Justin Pihony · Apr 13, 2012

If you have control over your schema, I would make the schema use cascading deletes.

From the article (the more pertinent portion translated for your example)

CREATE TABLE point
(
    pt_id integer PRIMARY KEY,
    evt_id integer REFERENCES event ON DELETE CASCADE
)

If you have cascades set up, then you can just delete from the main event table and all the other tables will be cleaned up automatically

Otherwise, you need to delete all of the references first, then you delete the main table. You should do this in one transaction to keep data consistent

BEGIN;
DELETE FROM trace WHERE EXISTS 
    (SELECT 1 FROM point WHERE evt_id = 1139 AND trace.pt_id = point.pt_id);
DELETE FROM point where evt_id = 1139;
DELETE FROM magnitude where evt_id = 1139;
DELETE FROM event where evt_id = 1139;
COMMIT;

Related questions

Delete rows with foreign key in PostgreSQL

I would like to delete rows which contain a foreign key, but when I try something like this: DELETE FROM osoby WHERE id_osoby='1' I get this statement: ERROR: update or delete on table "osoby" violates foreign key …

Read More
Postgres and Indexes on Foreign Keys and Primary Keys

Does Postgres automatically put indexes on Foreign Keys and Primary Keys? How can I tell? Is there a command that will return all indexes on a table?

Read More
postgresql foreign key syntax

I have 2 tables as you will see in my posgresql code below. The first table students has 2 columns, one for student_name and the other student_id which is the primary key. In my second table called tests, this has 4 …

Read More