Ok, so now you have learnt the lesson that not to define a primary key based on any attributes.
is it ok for ur customer if the values r updated at night ? if so, u can add a column to the table, say "new_customer_id", and then at night run thru a huge update to replace all the keys in referenced tables. if this is an acceptable soln, i would further suggest writing a procedure which uses dynamic sql to read the oracle data dictionary and updating all the tables using this key. also, u can use bulk binds to get ur updates done faster. and the hard way which u r saying is really not that hard. u would just need a wknd and get all ur updates run. hope this helps ! -----Original Message----- Sent: Monday, February 17, 2003 5:39 AM To: Multiple recipients of list ORACLE-L > We have a database application that uses a primary key field value of > CUSTOMER in several tables, and as a foreign key in others. Everything > works fine, but the customer has come up with a new requirement - they > wish to be able to rename these primary keys values on the fly. > > I'm looking for a simple way to do this, the problem is simply issuing a > load of updates would be a risky business because what could happen is ... > > 1) Part of the application reads a CUSTOMER value using a SELECT > statement and stores the value in a variable. > 2) I do the big update renaming CUSTOMER values everywhere they are > used & commit. > 3) The part of the application that still has the old values stored in > a variable and then writes the old value to a new record and the database > ends up with a mix of old and new values. > > One way to prevent this would be to lock all tables I want to update in 2) > before doing anything. This will only work if the lock prevents all SELECT > statements from working until I am done. > > From answers given on this list, it seems that SELECTs cannot be prevented > in this way. We are also not using any referential integrity constraints > so the update in 3) would work fine. > > Can anyone think of a simple solution, or do I have to do this the hard > way (rewrite whole thing to use a sequence number as the primary key, and > have Customer as an updatable bit of text). > > -----Original Message----- > From: John Dunn > Sent: 17 February 2003 08:58 > To: Jamie Pearce > Subject: > > << Message: Re: Lock table and disallow select >> << Message: > Re:Lock table and disallow select >> << Message: RE: Lock table and > disallow select >> << Message: Re:Lock table and disallow select >> << > Message: Re: Lock table and disallow select >> << Message: RE: Lock table > and disallow select >> << Message: RE: Lock table and disallow select >> > << Message: Re: Lock table and disallow select >> << Message: Re: Lock > table and disallow select >> << Message: RE: Lock table and disallow > select >> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
