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
