Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Preventing select on table

RE: Preventing select on table

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Mon, 17 Feb 2003 06:33:49 -0800
Message-ID: <F001.0054F3E2.20030217063349@fatcity.com>


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).

Received on Mon Feb 17 2003 - 08:33:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US