Re: help: how do you rename a primary key

It's a simple enough rule: 'Every time you see a primary or a unique key constraint declared, make it deferrable'.

Why? Because deferrable constraints (available since Oracle 8.0 onwards) are always enforced with a non-unique index. And when you disable the constraint, non-unique indexes are *not* dropped (but unique ones, as you've discovered, are).

The constraint doesn't even have to be actually deferred: it's the prospect that they might one day be deferred that causes Oracle to use a non-unique index. Hence 'deferrable initially immediate' is good enough to make the non-uniqueness behaviour happen.

There is zero performance penalty from using non-unique indexes to enforce unique or primary key constraints, so there's really nothing to lose from slapping the 'deferrable' key word in there.

In 9i, you get to state explicitly whether you want to retain the index at the time of a constraint disablement, even if it is a unique one. But until then, the 'deferrable' technique is your only option.

Don't forget, too, that from 8.0 onwards, it is possible to 'partially' enable a constraint, meaning 'alter table emp enable novalidate constraint blah' -at which point, the constraint is in force as far as new records inserted into the table are concerned, but there is no exclusive lock taken on the table for the duration of the validation of existing entries, simply because that validation doesn't happen. That means you can put off the expensive 28 minutes rebuild until late in the night when the incovenience to users will be minimal.

Regards
HJR

--
-----------------------------------------------
Resources for Oracle : http://www.hjrdba.com
===============================

"Joe Sath" <dbadba62_at_hotmail.com> wrote in message
news:2GLt8.4323$8W5.1339_at_nwrddc02.gnilink.net...


> 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

>

>

>