I have a table who's purpose is to store the application's various images. It is defined this way :
CREATE TABLE images ( id lo NOT NULL, name character varying(1024) NOT NULL, type character varying(32) NOT NULL, dimension point, last_modified timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT "PK_images" PRIMARY KEY (id) )
Some other tables have foreign keys referencing
images.id with constraints that an image should not be deleted if it is referenced somewhere (i.e. the app is responsible to properly delete the reference first);
... CONSTRAINT "FK_foo_image" FOREIGN KEY (image_id) REFERENCES images (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT ...
Since many table rows may reference the same image, I need to automatically cleanup images that no longer have any reference.
My first idea was to implement the application to delete all foreign key references, then remove all images matching the values of these deleted foreign keys. To do so, I'd create a trigger on the table
images, before delete, that could "swallow" all constraint exception if a query such as only "orphaned" images would be deleted and the others would be silently ignored. I'm just not sure how to properly do that.
If that idea is not good, what could be other (if not better) alternatives?
Basically, if I'm executing a query like
DELETE FROM images WHERE id IN (101, 102, 103, 104, 105)
104 have constraints that prevents them to be deleted, I'd like all other images to still be deleted. The
DELETE query should be simple as the one above.