Обсуждение: 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: > 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!