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;
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.
But image is used more than once, so its ideal to keep image independent from other tables.
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.
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:
Neither of these looking ideal to me.
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.
Yep, I guess I should control it from software side.