pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (7+ messages)  latest in thread

Message-ID: <39C810AF.94E90D99@ipinc.com>
Permalink:  ../39C810AF.94E90D99@ipinc.com/
Also on:    postgresql.org/message-id/39C810AF.94E90D99@ipinc.com

 · 

reply

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