Lost in Foreign Key land
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
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:
Show quoted text
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
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!