Received: from tidal.ipinc.com (tidal.ipinc.com [199.245.188.85]) by hub.org (8.10.1/8.10.1) with ESMTP id e8K1N8s05983; Tue, 19 Sep 2000 21:23:08 -0400 (EDT) Received: from ipinc.com (jliang.ipinc.com [199.245.188.130]) by tidal.ipinc.com (8.9.3/8.9.3) with ESMTP id SAA19396; Tue, 19 Sep 2000 18:22:59 -0700 (PDT) (envelope-from jliang@ipinc.com) Message-ID: <39C810AF.94E90D99@ipinc.com> Date: Tue, 19 Sep 2000 18:19:43 -0700 From: Jie Liang Organization: ipinc.com X-Mailer: Mozilla 4.7 [en] (X11; U; FreeBSD 3.4-RELEASE i386) X-Accept-Language: en MIME-Version: 1.0 To: Timothy Covell CC: pgsql-general@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [GENERAL] Foreign Keys Help Delete! References: <000371abbd90c460_mailit@smtp.applink.net> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200009/143 Hi, Timothy, Try: 1. dump out the data of routes and drop it, 2. re-build your routes table, CREATE TABLE routes ( field1 type1, fqhn stype2, CONSTRAINT if_host_exists FOREIGN KEY(fqhn) REFERENCES hosts ON UPDATE CASCADE ON DELETE CASCADE ); or use alter table add constraint ..... When update hosts's primary key, foreign key will be updated also, so does delete. However, I found foreign key of Pg7.0 is extremely slow for big table loading, because Pg constraint have no disable, novalidate choice, so it's not easy to use so far, I believe. Timothy Covell wrote: > DB: Postgresql 7.0.2 > OS: Solaris 2.6 > > Schema: > hosts table with fqhn column > routes table with fqhn foreign key hosts(fqhn) > > Problem: > > 1. When I try to change fqhn in hosts, it complains that > I have now violated entry in "routes" table. > > 2. When I try to update "routes" table, it updates. > > 3. Go back to "hosts" table and now try to rename/delete > old fqhn and it complains about object missing with OID=xxxxx. > > Questions: > > 1. What's the proper way to delete foreign keys? > (I can dump the DB, edit it, and restore it, but that is > not effecient!!) > > 2. How can I avoid problems such as above, besides not > using foreign keys? > > 3. Are foreign keys broken in pg7.0.2??? > > TIA > tim > dirac@applink.net -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com