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

Home -> Community -> Usenet -> c.d.o.server -> Re: help: how do you rename a primary key

Re: help: how do you rename a primary key

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Sat, 13 Apr 2002 08:47:26 GMT
Message-ID: <3cb7efa0.220082@news.jaapwvandijk.myweb.nl>


From Oracle 8 and onwards the Oracle docs recommends creating a nonunique index and then creating the primary key constraint (this is not yet allowed in Oracle 7). The nonunique index is being used to enforce the uniqueness. If you drop or disable the constraint, the index will not be dropped.

Jaap.

On Sat, 13 Apr 2002 01:16:46 GMT, "Joe Sath" <dbadba62_at_hotmail.com> wrote:

>OK,
>I take your guys' advice, not to change any data dictionary.
>But rebuild the parmary key using index take so long, sometime 28 minutes
>
>Whenever you drop the primary key, the unique index associated with it also
>got dropped.
>
>Is there any way we can only drop the constraint, w/o dropping the unique
>index with it on the same column?
>
>Thanks again for so many response.
>
>
>>Horrible advice. Drop it and recreate it from scratch.
>
>>I would terminate anyone reporting to me that made a >change to a
>dictionary
>>object.
>
>>Then again ... I wouldn't give them privileges that >allowed them to do it
>so
>>their jobs are pretty safe.
>
>?Daniel Morgan
>
>
>
>Paul Brewer wrote:
>
>> "Svend Jensen" <Svend_at_OracleCare.Com> wrote in message
>> news:3CB5D01F.80904_at_OracleCare.Com...
>> > Joe Sath wrote:
>> >
>> > > Hi, Folks
>> > >
>> > > I need to rename a primary key with a unick index. The table is big,
>if
>> I
>> > > drop the primary key and then rebuild it with "using index", it will
>> take a
>> > > while.
>> > >
>> > > Is there any way we can just rename it like we rename a index?
>> > >
>> > > Thanks for your help.
>> > >
>> > >
>> > >
>> >
>> > Yes, log in as sys, update con$ set name = new_name
>> > where con# = (select con# from con$
>> > where owner = 'constraint_owner'
>> > name = 'old_name')
>> >
>> > There is a unique index i_con2 on con$ (con#)
>> > and a unique index i_con1 on (owner, name)
>> >
>> > These cant be rebuild, they are part of instance warmstart, as you can
>> > se from bootstrap$
>> >
>> > I have used this method on 8.1.7 without any negative impact. But only
>> > if the need is there and no other solution could be applied while
>> > 'business as usual' was ongoing.
>> >
>> > rgds
>> >
>> > /svend
>> >
>> This is bad advice.
>>
>> Paul
>
>
>
Received on Sat Apr 13 2002 - 03:47:26 CDT

Original text of this message

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