Re: Implementing automatic updating of primary keys...

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(at)postgresql(dot)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.