Re: Preventing select on table

I disagree that it is inherently bad to EVER use a natural key.

In most cases, such as this one, it doesn't work very well. However, there are many cases where implementing the primary key as a surrogate key, simply to satisfy a blanket "thou shalt not use natural keys" is overkill and unnecessarily complicates things.

As a simple example, consider a table of US state codes to be used in addresses (STATE_CODE, STATE_NAME, ...). Is adding a sequence-generated STATE_ID really necessary? It is unlikely that CA will ever mean anything other than California or that California will demand that it be changed to CF. All that adding a surrogate key here does is complicate the application. The vast majority of users will never need to drill down to see what CA means, but to display anything useful on a screen or report, the surrogate key will always require a join.

Don Granaman
OraSaurus

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, February 17, 2003 1:33 PM

> 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: 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: <netmadcap_at_netzero.com
> INET: netmadcap_at_netzero.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).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Don Granaman
  INET: granaman_at_cox.net

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