Received: from svr4.applink.net (svr4.applink.net [216.91.192.4]) by hub.org (8.10.1/8.10.1) with ESMTP id e8L2SIs96149 for ; Wed, 20 Sep 2000 22:28:18 -0400 (EDT) Received: from riemann.ashavan.org (56k-01-78.applink.net [216.91.196.78]) by svr4.applink.net (8.10.1/8.10.1) with SMTP id e8L2RIg08467; Wed, 20 Sep 2000 21:27:18 -0500 Subject: Re: [GENERAL] Foreign Keys Help Delete! From: Timothy Covell Reply-To: Timothy Covell Message-ID: <000371c21bccd077_mailit@smtp.applink.net> References: <000371abbd90c460_mailit@smtp.applink.net> <39C7FFFB.6A9A0A2B@agliodbs.com> <000371ad62280d0d_mailit@smtp.applink.net> <39C8D9E7.9896E205@agliodbs.com> Date: Wed, 20 Sep 2000 21:30:34 -0500 X-Mailer: BeatWare Mail-It 3.0 X-BeOS-Platform: Intel or clone To: pgsql-sql@postgresql.org, josh@agliodbs.com Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit X-Archive-Number: 200009/160 >Timothy, Tom: > >> >1. a. Create new record with new key value in hosts table with the >> >desired value >> > b. Update the routes record to reference the new value >> > c. Delete the old record in the hosts table >> > >> >> Yes, that's what I tried. >> >> 1. foo.old.com exists in "hosts" table and "routes" table >> 2. create foo.new.com in "hosts" table >> 3. delete foo.old.com in "routes" table >> 4. add foo.new.com into "routes" table >> 5. try to delete foo.old.com and it complains! > >Tom - not to interrupt your coding :-) this sounds like a bug. Any >thoughts? No, Tom's not needed. I double checked things again today, and was able to delete the problem records today.... I'll blame it on gremlins. ;-) I suppose that I got lost in the data and the gremlins must have cleaned it up while I slept last night....;-) Sorry to get any feathers ruffled.... Still, I would like an easy way to drop and recreate foreign keys and from what I see, it will appear that there is not a "safe" way to do this yet. > >> >2. a. Drop the Foriegn Key constraint >> > b. Update both the routes and hosts tables >> > c. Re-establish the foriegn key constraint >> >> This is the part that I'm fuzzy on. I've tried this before >> with complete DB corruption resulting. I had to dump each table >> one by one, edit my schema with vi, create new DB, import tables >> one by one....very painful! > >This also sounds like a problem. One should be able to drop a >constraint, the re-create the restraint and check existing records >against it. You can do this in MSSQL and Oracle. > >> PPS. As I replied to Stephan, I'm contracting at a company and I >> don't have access to e-mail. Taking a schema home is NOT OK. >> I already asked the manager if I could GPL my DNS-DB implementation. >> As you might expect, the non-technical manager, didn't know what >> GPL was, and he was NOT going to allow my work to be released to >> public.... And of course, higher ups in company may decide that >> my solution breaks the "don't build if you can buy" policy, in which >> case all of my work is for naught! ARGH!!!!!! > >Well, if they don't use it, you can easily re-create your work at home >and GPL it. It also depends on the contract you signed ... Yes, well, gremlin infested work is not good to distribute onto the net...;-) Time will tell.... > > -Josh >-- >______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 436-9166 > for law firms, small businesses fax 436-0137 > and non-profit organizations. pager 338-4078 > San Francisco