Re: [GENERAL] Foreign Keys Help Delete!

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(at)applink(dot)net

--
Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang(at)ipinc(dot)com
www.ipinc.com