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;
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.