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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly