473,545 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Update of foreign key values

I have two tables in two databases (Pg 7.2.1 - yes I need to upgrade but
there are several other dependencies I have to resolve first) and I need
to update one database's tables so they can be merged into the other
database's table. I know I can drop the constraints and update the tables
(primary key, and foreign key) but was hoping I'd not have to do that.
An attempt at using the techniques in Joel Burton's "Referentia l
Integrity Tutorial & Hacking the Referential Integrity tables" was
unsuccessful.

Is there a method I can use to add 1000 to all the primary and foreign
keys in one pass? Searches using Google and the PostgreSQL docs turned up
nothing useful to my situation. I see if the table/constraint would have
been created differently the updates would have cascaded but that did
not happen.
TIA,
Rod
--
"Open Source Software - Sometimes you get more than you paid for..."

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #1
1 2580
Roderick A. Anderson wrote:
I have two tables in two databases (Pg 7.2.1 - yes I need to upgrade but
there are several other dependencies I have to resolve first) and I need
to update one database's tables so they can be merged into the other
database's table. I know I can drop the constraints and update the tables
(primary key, and foreign key) but was hoping I'd not have to do that.
An attempt at using the techniques in Joel Burton's "Referentia l
Integrity Tutorial & Hacking the Referential Integrity tables" was
unsuccessful.

Is there a method I can use to add 1000 to all the primary and foreign
keys in one pass? Searches using Google and the PostgreSQL docs turned up
nothing useful to my situation. I see if the table/constraint would have
been created differently the updates would have cascaded but that did
not happen.


Strikes me as a perfect example for having the constraint defined "ON
UPDATE CASCADE". Or making it "DEFERRABLE " and do both changes in a
transaction after setting the constraint deferred.
Jan

--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
5889
by: Fraser Hanson | last post by:
Hello, I have a table which has a foreign key relationship with itself. I want and expect my updates to cascade (deletes definitely cascade as expected) but instead I just get error 1217: foriegn key error. I have written example code to use in reproducing the problem: # Create the table
0
3758
by: john | last post by:
Hi, I'm using: - Red Hat 9 - MySQL 3.23.56 create table t1( id integer not null, primary key(id)) type = INNODB;
2
39417
by: Gunnar Vøyenli | last post by:
Hi! For the sake of simplicity, I have three tables, Employee, Department and Work Employee >---- Department \ / \ / ^ ^ Work
16
16979
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums must be UPDATED, if not, they must be INSERTED. Logically then, I would like to SELECT * FROM <TABLE> WHERE ....<Values entered here>, and then...
7
19381
by: robert | last post by:
i need to update a column which is a member of the PK on this table. there are some thousands of rows to be updated, many more thousand already in the table. so, i get a constraint violation doing a simple set col1 = 'x' where col1 = 'y' attempted (in general terms; col1 & col2 are the PK)
7
2408
by: PC Datasheet | last post by:
Looking for suggestions ---- A database was designed for a national automobile inspection program. In it's simplest form, the database has two tables: TblOwner OwnerID <Year/Make/Model owned by owner, Owner name/address, etc) TblInspection InspectionID
0
5803
by: M. David Johnson | last post by:
I cannot get my OleDbDataAdapter to update my database table from my local dataset table. The Knowledge Base doesn't seem to help - see item 10 below. I have a Microsoft Access 2000 database which indexes computer magazine articles for personal reference. I am developing a Visual Basic.NET program whose sole purpose is to enter new...
3
2269
by: dbuchanan | last post by:
Hello, (Windows forms - SQL Server) I fill my datagrid with a stored procedure that includes relationships to lookup tables so that users can see the values of the combobox selections rather than the key value that are stored in the table. It works well if the comboboxes are selected when the row is created.
1
3653
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into "access_log", the referential integrity triggers generate these queries: SELECT 1 FROM ONLY "public"."application_type" x
0
6148
by: David Linsin | last post by:
I created a simple test case to reproduce the problem (also check Bug #15500): import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; /** * Simple test program to reproduce bug
0
7398
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7805
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
5969
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5325
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3449
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3441
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1878
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1013
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
701
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.