Re: Lost in Foreign Key land

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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!