Thread: [GENERAL] Foreign Keys Help Delete!
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
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 informationtechnology 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
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?
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
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
informationtechnology 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
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.
>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
Hi,
I've been trying to do:
select col1 from table while array_col[1][1:4]='2';
how do I do this sort of thing? There seems to be no docs ;-(
my array is {{"1","2","4","2"},{"3","2","5"},{"6","3","7","9"}}
I would also like to know that if I have an array as a large object, is it
possible to do a search on it using rows and columns (or by any other
way)?
thanks,
Indraneel
/************************************************************************.
# Indraneel Majumdar ¡ E-mail: indraneel@123india.com #
# Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com #
# Centre for DNA Fingerprinting and Diagnostics, #
# Hyderabad, India - 500076 #
`************************************************************************/
On Thu, 21 Sep 2000, Indraneel Majumdar wrote:
> select col1 from table while array_col[1][1:4]='2';
>
> how do I do this sort of thing? There seems to be no docs ;-(
>
> my array is {{"1","2","4","2"},{"3","2","5"},{"6","3","7","9"}}
You'll want to check out the array utilities in the contrib directory.
They include element is member of array and other such functions and
will probably do what you need.
> I would also like to know that if I have an array as a large object, is it
> possible to do a search on it using rows and columns (or by any other
> way)?
You're putting array style data into a large object with the lo_
functions? Probably not in a meaningful way, no (although I'd guess that
toast might work for that kind of application when 7.1 comes out.)