From: "Rajesh Kumar Mallah." <mallah@trade-india.com>
Organization: Infocom Network Limited.
To: pgsql-sql@postgresql.org
Subject: Implementing automatic updating of primary keys...
Date: Thu, 16 Jan 2003 17:41:49 +0530
User-Agent: KMail/1.5
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200301161741.49024.mallah@trade-india.com>


Hi we are working on re-structuring our database schemas and 
intend to implement the functionality below at database level.

consider a master table with following data.


Table: profile_master
------------

id | username | password
---|----------|----------
1  |   u1     | p1
2  |   u2     | p2

id--> primary key not null.


table t1 
-----------

id |  service_id
---|------------
1  |  1
2  | 10

table t2
--------------

id | rfi_id
---|-----------
1  | 1001
2  | 23

there can be many  such tables that have foreign key id which is referencing
the master table test_master column "id". what we want is when some ids become 
redundant we have to merge two ids , we want that thru out the system the 
occurance of the old primary key shud be removed and replaced with the new id.

so if id  2 were to be mergered to id 1 then the tables shud look like:

Table: profile_master
------------

id | username | password
---|----------|----------
1  |   u1     | p1


id--> primary key not null.


table t1 
-----------

id |  service_id
---|------------
1  |  1
1  | 10

table t2
--------------

id | rfi_id
---|-----------
1  | 1001
1  | 23

can some thing be done in the database level it self so that we do not have
to keep modifying the mantainence programs as the number of tables referencing 
master table grows?

regds
mallah.


-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.