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
ONUPDATE 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