pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign Keys Help Delete!
7+ messages / 4 participants
[nested] [flat]
* Foreign Keys Help Delete!
@ 2000-09-19 23:49 Timothy Covell <dirac@applink.net>
2000-09-20 00:08 ` Re: [GENERAL] Foreign Keys Help Delete! Josh Berkus <josh@agliodbs.com>
2000-09-20 00:37 ` Re: [SQL] Foreign Keys Help Delete! Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-09-20 01:19 ` Re: [GENERAL] Foreign Keys Help Delete! Jie Liang <jliang@ipinc.com>
0 siblings, 3 replies; 7+ messages in thread
From: Timothy Covell @ 2000-09-19 23:49 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] Foreign Keys Help Delete!
2000-09-19 23:49 Foreign Keys Help Delete! Timothy Covell <dirac@applink.net>
@ 2000-09-20 00:08 ` Josh Berkus <josh@agliodbs.com>
2000-09-20 15:38 ` Re: [GENERAL] Foreign Keys Help Delete! Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2000-09-20 00:08 UTC (permalink / raw)
To: pgsql-sql
Mr. Covell,
> 2. When I try to update "routes" table, it updates.
Actually, what I'm curious about is this part. Most databases that
support foriegn keys will not allow you to modify them as long as a
relation exists referencing the key, on either the master or child side,
unless you are updating the child to NULL (if the column is nullable) or
a valid alternative forign key value.
If you have updated the child record so that no records reference the
master key value, that key value should be then updatable without
violating the Forign Key constraint. However, I have not had reason to
test this on 7.0.2.
This provides you with two approaches for updating BOTH hosts and routes
table:
1. a. Create new record with new key value in hosts table with the
desired value
b. Update the routes record to reference the new value
c. Delete the old record in the hosts table
2. a. Drop the Foriegn Key constraint
b. Update both the routes and hosts tables
c. Re-establish the foriegn key constraint
If either of these approaches doesn't work, you have a valid bug
report. COngratulations!
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] Foreign Keys Help Delete!
2000-09-19 23:49 Foreign Keys Help Delete! Timothy Covell <dirac@applink.net>
2000-09-20 00:08 ` Re: [GENERAL] Foreign Keys Help Delete! Josh Berkus <josh@agliodbs.com>
@ 2000-09-20 15:38 ` Josh Berkus <josh@agliodbs.com>
2000-09-20 18:21 ` Re: [GENERAL] Foreign Keys Help Delete! Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-09-21 02:30 ` Re: [GENERAL] Foreign Keys Help Delete! Timothy Covell <dirac@applink.net>
0 siblings, 2 replies; 7+ messages in thread
From: Josh Berkus @ 2000-09-20 15:38 UTC (permalink / raw)
To: Timothy Covell <dirac@applink.net>; pgsql-sql
Timothy, Tom:
> >1. a. Create new record with new key value in hosts table with the
> >desired value
> > b. Update the routes record to reference the new value
> > c. Delete the old record in the hosts table
> >
>
> Yes, that's what I tried.
>
> 1. foo.old.com exists in "hosts" table and "routes" table
> 2. create foo.new.com in "hosts" table
> 3. delete foo.old.com in "routes" table
> 4. add foo.new.com into "routes" table
> 5. try to delete foo.old.com and it complains!
Tom - not to interrupt your coding :-) this sounds like a bug. Any
thoughts?
> >2. a. Drop the Foriegn Key constraint
> > b. Update both the routes and hosts tables
> > c. Re-establish the foriegn key constraint
>
> This is the part that I'm fuzzy on. I've tried this before
> with complete DB corruption resulting. I had to dump each table
> one by one, edit my schema with vi, create new DB, import tables
> one by one....very painful!
This also sounds like a problem. One should be able to drop a
constraint, the re-create the restraint and check existing records
against it. You can do this in MSSQL and Oracle.
> PPS. As I replied to Stephan, I'm contracting at a company and I
> don't have access to e-mail. Taking a schema home is NOT OK.
> I already asked the manager if I could GPL my DNS-DB implementation.
> As you might expect, the non-technical manager, didn't know what
> GPL was, and he was NOT going to allow my work to be released to
> public.... And of course, higher ups in company may decide that
> my solution breaks the "don't build if you can buy" policy, in which
> case all of my work is for naught! ARGH!!!!!!
Well, if they don't use it, you can easily re-create your work at home
and GPL it. It also depends on the contract you signed ...
-Josh
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] Foreign Keys Help Delete!
2000-09-19 23:49 Foreign Keys Help Delete! Timothy Covell <dirac@applink.net>
2000-09-20 00:08 ` Re: [GENERAL] Foreign Keys Help Delete! Josh Berkus <josh@agliodbs.com>
2000-09-20 15:38 ` Re: [GENERAL] Foreign Keys Help Delete! Josh Berkus <josh@agliodbs.com>
@ 2000-09-20 18:21 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2000-09-20 18:21 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Timothy Covell <dirac@applink.net>; pgsql-sql
On Wed, 20 Sep 2000, Josh Berkus wrote:
> Timothy, Tom:
>
> > >1. a. Create new record with new key value in hosts table with the
> > >desired value
> > > b. Update the routes record to reference the new value
> > > c. Delete the old record in the hosts table
> > >
> >
> > Yes, that's what I tried.
> >
> > 1. foo.old.com exists in "hosts" table and "routes" table
> > 2. create foo.new.com in "hosts" table
> > 3. delete foo.old.com in "routes" table
> > 4. add foo.new.com into "routes" table
> > 5. try to delete foo.old.com and it complains!
>
> Tom - not to interrupt your coding :-) this sounds like a bug. Any
> thoughts?
Probably doesn't need to go all the way to Tom... :)
Hmm, on my 7.0.2 box,
sszabo=# create table hosts (fqdn varchar(30));
CREATE
sszabo=# create table routes (fqdn varchar(30),foreign key(fqdn)
references hosts(fqdn));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
sszabo=# insert into hosts values ('foo.old.com');
INSERT 181159 1
sszabo=# insert into routes values ('foo.old.com');
INSERT 181160 1
sszabo=# begin;
BEGIN
sszabo=# insert into hosts values ('foo.new.com');
INSERT 181161 1
sszabo=# delete from routes where fqdn='foo.old.com';
DELETE 1
sszabo=# insert into routes values ('foo.new.com');
INSERT 181162 1
sszabo=# delete from hosts where fqdn='foo.old.com';
DELETE 1
sszabo=# end;
COMMIT
--
To original complainant:
Since you won't be able to post the trigger information either probably,
can you check pg_trigger to make sure there are no dangling constraint
triggers?
You should have three rows that look like:
181144 | RI_ConstraintTrigger_181153 | 1644 | 21 | t | t
| <unnamed> | 181120 | f | f | 6 |
| <unnamed>\000routes\000hosts\000UNSPECIFIED\000fqdn\000fqdn\000
181120 | RI_ConstraintTrigger_181155 | 1654 | 9 | t | t
| <unnamed> | 181144 | f | f | 6 |
| <unnamed>\000routes\000hosts\000UNSPECIFIED\000fqdn\000fqdn\000
181120 | RI_ConstraintTrigger_181157 | 1655 | 17 | t | t
| <unnamed> | 181144 | f | f | 6 |
| <unnamed>\000routes\000hosts\000UNSPECIFIED\000fqdn\000fqdn\000
Except that the relation oids are likely to be different (important ones
are the tgrelid and tgconstrrelid). The function oids (1644, 1654, 1655)
should be the same I believe.
> > >2. a. Drop the Foriegn Key constraint
> > > b. Update both the routes and hosts tables
> > > c. Re-establish the foriegn key constraint
> >
> > This is the part that I'm fuzzy on. I've tried this before
> > with complete DB corruption resulting. I had to dump each table
> > one by one, edit my schema with vi, create new DB, import tables
> > one by one....very painful!
>
> This also sounds like a problem. One should be able to drop a
> constraint, the re-create the restraint and check existing records
> against it. You can do this in MSSQL and Oracle.
Well, we don't have ALTER TABLE ... DROP CONSTRAINT right now. Dropping
the constraint requires removing the triggers manually. We can do an
ADD CONSTRAINT which will check the data, but not the corresponding DROP.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] Foreign Keys Help Delete!
2000-09-19 23:49 Foreign Keys Help Delete! Timothy Covell <dirac@applink.net>
2000-09-20 00:08 ` Re: [GENERAL] Foreign Keys Help Delete! Josh Berkus <josh@agliodbs.com>
2000-09-20 15:38 ` Re: [GENERAL] Foreign Keys Help Delete! Josh Berkus <josh@agliodbs.com>
@ 2000-09-21 02:30 ` Timothy Covell <dirac@applink.net>
1 sibling, 0 replies; 7+ messages in thread
From: Timothy Covell @ 2000-09-21 02:30 UTC (permalink / raw)
To: pgsql-sql; josh@agliodbs.com
>Timothy, Tom:
>
>> >1. a. Create new record with new key value in hosts table with the
>> >desired value
>> > b. Update the routes record to reference the new value
>> > c. Delete the old record in the hosts table
>> >
>>
>> Yes, that's what I tried.
>>
>> 1. foo.old.com exists in "hosts" table and "routes" table
>> 2. create foo.new.com in "hosts" table
>> 3. delete foo.old.com in "routes" table
>> 4. add foo.new.com into "routes" table
>> 5. try to delete foo.old.com and it complains!
>
>Tom - not to interrupt your coding :-) this sounds like a bug. Any
>thoughts?
No, Tom's not needed. I double checked things again today, and
was able to delete the problem records today.... I'll blame it on
gremlins. ;-) I suppose that I got lost in the data and the
gremlins must have cleaned it up while I slept last night....;-)
Sorry to get any feathers ruffled....
Still, I would like an easy way to drop and recreate foreign
keys and from what I see, it will appear that there is not a
"safe" way to do this yet.
>
>> >2. a. Drop the Foriegn Key constraint
>> > b. Update both the routes and hosts tables
>> > c. Re-establish the foriegn key constraint
>>
>> This is the part that I'm fuzzy on. I've tried this before
>> with complete DB corruption resulting. I had to dump each table
>> one by one, edit my schema with vi, create new DB, import tables
>> one by one....very painful!
>
>This also sounds like a problem. One should be able to drop a
>constraint, the re-create the restraint and check existing records
>against it. You can do this in MSSQL and Oracle.
>
>> PPS. As I replied to Stephan, I'm contracting at a company and I
>> don't have access to e-mail. Taking a schema home is NOT OK.
>> I already asked the manager if I could GPL my DNS-DB implementation.
>> As you might expect, the non-technical manager, didn't know what
>> GPL was, and he was NOT going to allow my work to be released to
>> public.... And of course, higher ups in company may decide that
>> my solution breaks the "don't build if you can buy" policy, in which
>> case all of my work is for naught! ARGH!!!!!!
>
>Well, if they don't use it, you can easily re-create your work at home
>and GPL it. It also depends on the contract you signed ...
Yes, well, gremlin infested work is not good to distribute onto the
net...;-) Time will tell....
>
> -Josh
>--
>______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 436-9166
> for law firms, small businesses fax 436-0137
> and non-profit organizations. pager 338-4078
> San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Foreign Keys Help Delete!
2000-09-19 23:49 Foreign Keys Help Delete! Timothy Covell <dirac@applink.net>
@ 2000-09-20 00:37 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2000-09-20 00:37 UTC (permalink / raw)
To: Timothy Covell <dirac@applink.net>; +Cc: pgsql-general@postgresql.org; pgsql-sql
On Tue, 19 Sep 2000, Timothy Covell wrote:
> 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.
Can you give the table structure and statements you were
doing specifically?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [GENERAL] Foreign Keys Help Delete!
2000-09-19 23:49 Foreign Keys Help Delete! Timothy Covell <dirac@applink.net>
@ 2000-09-20 01:19 ` Jie Liang <jliang@ipinc.com>
2 siblings, 0 replies; 7+ messages in thread
From: Jie Liang @ 2000-09-20 01:19 UTC (permalink / raw)
To: Timothy Covell <dirac@applink.net>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2000-09-21 02:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-19 23:49 Foreign Keys Help Delete! Timothy Covell <dirac@applink.net>
2000-09-20 00:08 ` Josh Berkus <josh@agliodbs.com>
2000-09-20 15:38 ` Josh Berkus <josh@agliodbs.com>
2000-09-20 18:21 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-09-21 02:30 ` Timothy Covell <dirac@applink.net>
2000-09-20 00:37 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-09-20 01:19 ` Jie Liang <jliang@ipinc.com>
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