RE: Preventing select on table

If you want to change the primary key, then it shouldn't be primary key in the first place.

Surrogate keys come to my mind ...

Raj

-----Original Message-----
Sent: Monday, February 17, 2003 6: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: john.dunn_at_sefas.co.uk

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: ListGuru_at_fatcity.com (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: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

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: ListGuru_at_fatcity.com (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).



text/plain attachment: ESPN_Disclaimer.txt