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 [EMAIL PROTECTED] -- 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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
