On Sat, Apr 09, 2005 at 03:41:55PM -0700, Benjamin Smith wrote: > Ok, I have a stupid-simple table: > create table files ( > id serial unique not null, > mime varchar not null, > name varchar not null > ); BTW, the hip kids use TEXT instead of VARCHAR :) > Idea being that I can save values from a file upload into a table, > and use throughout my application. This gives me a central > repository to look for files of a particular name, etc. while > allowing multiple files with the same name to be uploaded. It might > be used something like this: > create table personalinfo ( > name varchar, > address varchar, > resume integer not null references files(id) > ); > But, I want to keep it "clean" - meaning that if the file orphaned, > (isn't used anywhere), I want to delete it. I asked a related > question a few months ago, and never understood the responses. (See > thread "Search for restricting foreign keys") I just spent the last > few hours trying to make sense of Forian Plug's query from an email > dated 1/25/05 and reading up on the attribute tables, and I am lost. OK, here's what you could do. For each table (like personalinfo) you have a trigger on INSERT OR UPDATE OR DELETE 1. Create a file_fk_ref table, which contains two columns: a file_id and a counter. You can either put a trigger which causes a DELETE on the files table for each row when its count reaches zero, or you could check regularly for zero'd file_ids and delete them batchwise. 2. Create triggers on each of the referencing tables (personalinfo, etc.) which SELECT the appropriate rows in file_fk_ref FOR UPDATE, then do the following for each row: * ON INSERT, increment the counter for the file_id in file_fk_ref. * ON UPDATE, check whether an increment & corresponding decrement are required on the file_fk_ref table. * ON DELETE, decrement the counter for the file_id in the file_fk_ref table. HTH :) Cheers, D -- David Fetter david@xxxxxxxxxx http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
