Thread: Implementing automatic updating of primary keys...

Implementing automatic updating of primary keys...

From
"Rajesh Kumar Mallah."
Date:
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.




Re: Implementing automatic updating of primary keys...

From
Tomasz Myrta
Date:
Rajesh Kumar Mallah. wrote:

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

If I understood well you want to change id in all tables from some value into another one and
no matter, how many these tables exist?

First - if your tables are created with "on update cascade", you can just change value on master table.

If you didn't create tables with this option and referencing key has the same name in all tables, 
it isn't still too difficult.

Everything you need is a function which finds all tables with field "id" and for each table performs:
update <table> set id=newvalue where id=oldvalue.

In plpgsql it will look something like:
create or replace function...
declareoldvalue alias for $1;newvalue alias for $2;tablename varchar;
begin for tablename in SELECT relname from pg_attribute join pg_class on (attrelid=oid)    where attname=''id'' and
relkind='r';loop   perform ''update '' || tablename '' set id='' || newvalue || '' where id='' || oldvalue; end loop;
 
end;

Many interesting find about database special tables you will find in 
Chapter 3. System Catalogs inside Postgresql documentation.

Regards,
Tomasz Myrta




Re: Implementing automatic updating of primary keys...

From
"Rajesh Kumar Mallah."
Date:

yes you got my problem rightly.

If i use "on update cascade" approach still
there is problem.

If i attempt to update the key in master table it 
wont be  allowed becoz of temporary violation of 
PRIMARY KEY CONSTRAINT.

becoz 1 is also existing in the master table.
update profile_master set id=1 where id=2 will
not be accepted.

regds
mallah.



On Wednesday 01 January 2003 06:11 pm, Tomasz Myrta wrote:
> Rajesh Kumar Mallah. wrote:
> >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.
> >
> >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.
>
> If I understood well you want to change id in all tables from some value
> into another one and no matter, how many these tables exist?
>
> First - if your tables are created with "on update cascade", you can just
> change value on master table.
>
> If you didn't create tables with this option and referencing key has the
> same name in all tables, it isn't still too difficult.
>
> Everything you need is a function which finds all tables with field "id"
> and for each table performs: update <table> set id=newvalue where
> id=oldvalue.
>
> In plpgsql it will look something like:
> create or replace function...
> declare
>  oldvalue alias for $1;
>  newvalue alias for $2;
>  tablename varchar;
> begin
>   for tablename in SELECT relname from pg_attribute join pg_class on
> (attrelid=oid) where attname=''id'' and relkind='r';
>   loop
>     perform ''update '' || tablename '' set id='' || newvalue || '' where
> id='' || oldvalue; end loop;
> end;
>
> Many interesting find about database special tables you will find in
> Chapter 3. System Catalogs inside Postgresql documentation.
>
> Regards,
> Tomasz Myrta
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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




Re: Implementing automatic updating of primary keys...

From
Bhuvan A
Date:
> 
> 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                   ^^^^^^^^^^^
It should be profile_master, right?

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

Definitely it can be done in the database level. Trigger a function before
delete on the primary table (profile_master) and update the foreign tables
with appropriate id and so the redundant id can be deleted from the
primary table. 

regards,
bhuvaneswaran