From: Rajesh Kumar Mallah. <mallah@trade-india.com>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Implementing automatic updating of primary keys...
Date: Thu, 16 Jan 2003 19:58:17 +0530
Message-ID: <200301161958.17064.mallah@trade-india.com> (raw)
In-Reply-To: <3E12E207.9020504@klaster.net>
References: <200301161741.49024.mallah@trade-india.com>
<3E12E207.9020504@klaster.net>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah@trade-india.com, jasiek@klaster.net
Subject: Re: Implementing automatic updating of primary keys...
In-Reply-To: <200301161958.17064.mallah@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox