Обсуждение: Implementing automatic updating of primary keys...
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.
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
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.
> > 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