From: Jie Liang <jliang@ipinc.com>
To: Timothy Covell <dirac@applink.net>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [GENERAL] Foreign Keys Help Delete!
Date: Tue, 19 Sep 2000 18:19:43 -0700
Message-ID: <39C810AF.94E90D99@ipinc.com> (raw)
References: <000371abbd90c460_mailit@smtp.applink.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: jliang@ipinc.com, dirac@applink.net, pgsql-general@postgresql.org
Subject: Re: [GENERAL] Foreign Keys Help Delete!
In-Reply-To: <39C810AF.94E90D99@ipinc.com>
References: <000371abbd90c460_mailit@smtp.applink.net> <39C810AF.94E90D99@ipinc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox