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