Delete data from child tables

Question

I have 2 tables:

"customers" and "addresses". A customer can have several addresses, so they have an "n:m" relationship. For this reason, I also have the table "customer-addr".

This is how my tables look like:

                    +---------------+       
+-----------+       | customer_addr |       
| customers |       +---------------+       +-----------+
+-----------+       | id            |       | addresses |
| id        | <---> | cid           |       +-----------+
| name      |       | aid           | <---> | id        |
+-----------+       +---------------+       | address   |
                                            +-----------+

I need to update all customer-data incl. all addresses. For this reason I thought about deleting all existing addresses first, then updating the customer-table, and after that, I create every address new.

My question: How can I delete all existing addresses from one customer efficiently? (I have to remove rows from 2 tables).

Is there a single-statement I can use? (Without the cascade-method, this is too risky)

Or can I do it with 2 statements, without using subselects?

What's the best approach for this?

Notice that I'm using postgresql

Edit:

My whole database-design is more complex, and the address-table is not only a child from "customers" but also from "suppliers","bulkbuyers",..

Every address belongs to only one customer OR one supplier OR one bulkbuyer. (No address is used by more than one parent / no address-sharing)

Ever customer/supplier/.. can have multiple addresses.

For this reason, the edited solution from zebediah49 won't work, because it would also delete all addresses from every supplier/bulkbuyer/...

If it's all data, can't you just drop the entire content of `customer_addr` and `addresses` ? EDIT:OK, you want for a single customer. Edit fixed my question; thanks. — zebediah49, Aug 01 '13 at 16:13
I only want to delete the data for one specific customer. And I only know the customer's id — maja, Aug 01 '13 at 16:14
How do you expect your data to differ after you do all this? — Dan Bracuk, Aug 01 '13 at 16:22
@Dan Bracuk: After every operation, the following changes could have happened: (1) customer-Data (eg. other customer-name) (2) address-Data (eg. other street) (3) new addresses added (4) old address removed (5) a combination of 1-4. So after this operation, everything customer-related could have changed — maja, Aug 01 '13 at 16:26
@Dan Bracuk: Do you mean the address-id? The column "id" is unique for every address and the primary key. All other data can be changed. — maja, Aug 01 '13 at 16:38

Erwin Brandstetter · Accepted Answer · 2013-08-01T18:30:24.630

2

I would use a writable CTE also called data-modifying CTE in PostgreSQL 9.1 or later:

WITH del AS (
   DELETE FROM customer_addr
   WHERE  cid = $kill_this_cid
   RETURNING aid
   )
DELETE FROM addresses a
USING  (SELECT DISTINCT aid FROM del) d
WHERE  a.id = d.aid;

This should be fastest and safest.

If (cid, aid) is defined UNIQUE in customer_addr you don't need the DISTINCT step:

...
DELETE FROM addresses a
USING  del d
WHERE  a.id = d.aid;

edited Aug 01 '13 at 18:30

answered Aug 01 '13 at 16:41

Erwin Brandstetter

605,456
145
1,078
1,228

Wow, I have no idea how this is working, but it does exactly what I want. I'll do some research how it works, thanks for the fast answer – maja Aug 01 '13 at 16:56
@maja: Your research can be as simple as following the link to the manual I included. ;) – Erwin Brandstetter Aug 01 '13 at 17:01
@maja: Concerning your rejected edit: The subquery is required to have an alias. But I applied your fix for the column name `id` to fit the question, even if I despise that. [Why?](http://stackoverflow.com/questions/9875223/auto-increment-sql-function/9875517#9875517) – Erwin Brandstetter Aug 01 '13 at 17:40
You're right, didn't think of that. I edited it to reach the minimum of 6 character-changes per edit. But the opening-bracket after "AS" is still missing – maja Aug 01 '13 at 18:18

zebediah49 · Answer 2 · 2013-08-01T16:35:58.907

1

EDIT:

Got it; this is safer because of the risk of two customers sharing an address anyway:

DELETE FROM customer_addr WHERE cid = $TARGET_CID;
DELETE FROM addresses WHERE id NOT IN (SELECT aid FROM customer_addr);

First, delete all references, then delete all unreferenced addresses. Note that you could, for example, only do the first step, and run the "cleanup" second step at a later time.

I would suggest a two step transaction:

DELETE FROM addresses WHERE id IN (SELECT ca.aid FROM customers c LEFT JOIN customer_addr ca ON ca.cid=c.id WHERE c.name='$NAME_TO_DELETE');
DELETE FROM customer_addr WHERE cid = (SELECT id FROM customers WHERE name='$NAME_TO_DELETE');

If you have the customer ID already (EDIT: You do), you can skip most of that:

DELETE FROM addresses WHERE id IN (SELECT aid FROM customer_addr WHERE cid=$TARGET_CID);
DELETE FROM customer_addr WHERE cid = $TARGET_CID;

Wrap those with the appropriate transactional BEGIN/END, to make sure that you don't end up in an inconsistent state, and you should be set.

edited Aug 01 '13 at 16:35

answered Aug 01 '13 at 16:18

zebediah49

7,467
1
33
50

This won't work, because I can't delete data from addresses, as long as the corresponding rows in customer_addr exists. The customer_addr -entries are still pointing to the addresses (foreign-key constraint) – maja Aug 01 '13 at 16:22
It won't work even if it's done within a single transaction? Interesting; I'll see if I can come up with an alternative. – zebediah49 Aug 01 '13 at 16:30
Unfortunatelly this won't work either, see my edited question :( – maja Aug 01 '13 at 16:47
You could always use a `NOT IN (... UNION ... )` but that leaves far more space for mistakes, and further updating later if you add a new dependent table. Given the updated information, the other answer is a better method. Alternatively (don't actually do this), you can trust your foreign keys, and just `DELETE FROM addresses`, with every referenced address throwing an error instead of getting deleted -- you'll also need to make sure it keeps going instead of quitting on the first invalid delete. – zebediah49 Aug 01 '13 at 17:49

Delete data from child tables

2 Answers2

EDIT: