Re: Lost in Foreign Key land

This is only a soft answer. A real answer would probably involve
mucking with the system tables, so maybe a question about the
application logic can prevent you from resorting to that.

Through what process does a file get orphaned?

Is it possible that you could just cascade deletes on the columns (like
resume) that refer to files, or can more than one table at a time
reference the same file? I.e., is there any reason you couldn't define
resume in your example to say ON DELETE CASCADE?

If multiple simultaneous references are valid, could you do basic
reference counting? E.g., create a refcount column in files that
tracked how many different tables pointed to it? Then, in a delete from
a referencing table, you could decrement the refcount. This would allow
you to delete from files WHERE refcount = 0.

Just some prelimnary thoughts...

-tfo

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

On Apr 9, 2005, at 5:41 PM, 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
> );
>
> 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.
>
> I'm sure it's very logical, and I'm just as sure that the logic, for
> now,
> escapes me. What I'd like to be able to do is get a list of files
> table id
> fields that have no values tied to them.
>
> If I execute "delete from files;", it won't delete them, because of
> foreign
> keys that refer to one or more of the files records.
>
> How can I get a list of files records with no foreign key records
> referencing
> the id field, without having to join on every single table that refers
> to
> files(id)? (now maybe a dozen, and growing) Something like "select id
> from
> files where id not in (select references to files.id)";
>
> -Ben
> --
> "The best way to predict the future is to invent it."
> - XEROX PARC slogan, circa 1978
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings