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@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
