Delete entries from multiple tables in PostgreSQL

Question

I have 3 tables in my postgreSQL

item_table:

id  item_id
------------
1   1234
2   5678

account_table:

item_id     account_id
----------------------
2       abcd

payment_table:

account_id  payment_status
--------------------------
abcd        good

Description

item_id in the account_table is the id of item_table
account_id of the payment_table is the account_id of account_table

I want to delete the entry from all tables associated with item_id from the item_table

Example : If my item_id is 5678 then I want to delete entries from all tables in which the ids are connected to each other.

How to achieve this in PostgreSQL?

Mike Sherrill 'Cat Recall' · Accepted Answer · 2022-04-22 14:55:59Z

2

If account.item_id is declared as a foreign key to item.id, you might declare that foreign key as 'ON DELETE CASCADE'. Then deleting a row from 'item' would cascade the deletion 'account'.

Repeat as needed for other tables.

It could be I've misunderstood you, but it's easy enough for you to test.

answered Apr 22, 2022 at 14:55

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sravan JS Over a year ago

None of these tables are connected to each other using foreign key. Is there a solution in that case?

Mike Sherrill 'Cat Recall' Over a year ago

@SravanJS: Worst case, run one DELETE statement per table, probably wrapped in a transaction. Wrap that in a function.

Belayer Over a year ago

Tables are not connected by FK. Is there a solution in this case? Yes, add the missing FKs. Without them you have data integrity issues - meaning bad data. After all with the values posted what the value of a row in payment table with account_id =' xyz', or an account table row with item_id = 42? They are just wrong and can generate correct but invalid/misleading results. FK would prevent them from being in the database.

user330315 · Accepted Answer · 2022-04-22 16:02:59Z

1

If you need to delete from three tables then you need to run three DELETE statements:

delete from payment_table
where account_id in (select account_id
                     from account_table
                     where item_id in (select id 
                                       from item_table
                                       where item_id = 5678));

delete from account_table
where item_id in (select id 
                  from item_table
                  where item_id = 5678);

delete from item_table
where item_id = 5678;

answered Apr 22, 2022 at 16:02

user330315

Collectives™ on Stack Overflow

Delete entries from multiple tables in PostgreSQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related