this post was submitted on 18 Nov 2023
25 points (100.0% liked)

PostgreSQL

655 readers
1 users here now

The world's most advanced open source relational database

Project
Events
Podcasts
Related Fediverse communities

founded 1 year ago
MODERATORS
Ategon
starman
jnovinger
25
Ways to remove child when parent deleted in 1:1 relation (lemy.lol)
submitted 11 months ago* (last edited 11 months ago) by [email protected] to c/postgresql
8 comments fedilink hide all child comments
 

Example flow is on the image. Here I want to accomplish if playlist removed, make it delete image too.

I know I should've put FK on image table but Image is generic and its used more than once.

What are my options? Triggers and application-side comes to mind, but I'm not sure. Maybe there is a better way.

UPDATE: I guess I've found what I was looking for, rules:

CREATE RULE playlist_delete AS ON DELETE TO playlist WHERE (SELECT id FROM image WHERE image.id = OLD.image_id) IS NOT NULL DO INSTEAD NOTHING;

all 9 comments
sorted by: hot top controversial new old
[–] [email protected] 3 points 11 months ago (1 children)

Looks like a trigger might be the right approach

https://stackoverflow.com/questions/53446075/cascading-delete-without-foreign-key-constraint-postgresql#53447913

[–] [email protected] 1 points 11 months ago

I've been avoiding triggers, but I guess there's no better alternative. Thanks.

[–] [email protected] 1 points 11 months ago (1 children)

Well, if you reverse the direction of the relationship, you can have it cascading on delete. And since it's 1:1, the direction isn't very impactful.

[–] [email protected] 2 points 11 months ago (1 children)

But image is used more than once, so its ideal to keep image independent from other tables.

[–] [email protected] 2 points 11 months ago* (last edited 11 months ago) (1 children)

Well, ok, that inviabilizes it.

How do you guarantee that each imagine is only used in a single place? From the model itself, you can't get that guarantee, and it makes sense to remove them on the same layer that checks this.

On your edit, are you really avoiding triggers but have no such avoidance for rules? Yes, those rules of thumb depend on lots and lots of details, and it's easy to have a non-standard situation... but on the general case, that strikes me as an upside-down decision.

[–] [email protected] 1 points 11 months ago (1 children)

What do you suggest for not using an image more than once? It bothers me too that image table is in non-standard use.

The thing is, image table used on 4 other tables and if I want to put FK on image table I have to:

  • Add 4 optional foreign keys to the image table and use only one
  • Create 4 different image table that each of them related to the related 4 tables

Neither of these looking ideal to me.

[–] [email protected] 2 points 11 months ago (1 children)

If you use the images in more than one place, you just can't automatically remove them when you remove some place they are used.

You can run some garbage collection where you look at every place an image can go, and remove the ones that are never used. You can even run that inline, even though it's better offline, but it's not the same as you asked on the title. Thus I imagine you have some software layer where there is a barrier that avoids using an image in more than a single place.

[–] [email protected] 1 points 11 months ago

Yep, I guess I should control it from software side.