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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with dropping a Primary Key and his Index on Oracle 10R1

Re: Problem with dropping a Primary Key and his Index on Oracle 10R1

From: A. Peters <info_no_spam_at_ap-data.de>
Date: Fri, 17 Feb 2006 15:41:25 +0100
Message-ID: <dt4naf$dh5$00$1@news.t-online.com>


Mark C. Stock wrote:

>
> "A. Peters" <info_no_spam_at_ap-data.de> wrote in message
> news:ds4rld$5di$02$1_at_news.t-online.com...
> >
> > Hello!
> > I4ve a Problem with Oracle 10 R1 and need some help.
> > I have installed a Oracle 10 R1 on a Testsystem and imported a
> > Databaseuser (with Tables etc...)
> >
> > now i want to update the structure of a table and droped the Primary
> > Key and oracle dropped the pk. But he doesnt dropped
> > the INDEX from the Primary Key. He leaves them on the column.
> >
> > when i create a Primary Key for a table on Oracle 10 R1 and drop the
> > Primary key the index is dropped to.
> > That he doesn4t drop the INDEX happend only when the primary key is
> > created on a previous Version of Oracle
> > like Oracle 8 or Oracle 9.
> >
> > I need to solve the problem, because we have customers with
> > differenz Oracle Versions and all shoudt get the
> > some SQL Statements to update her Database Structure.
> >
> > Greetings
> > A. Peters
> > --
>
> if the index used for the primary key is created before the primary
> key is created, oracle will not drop the (pre-existing) index when
> dropping the primary key
>

The Index is created by the Primary Key.

> this is good if the index is purposely created separately from the
> primary key (the index could be non-unique, and could by multi-column
> beyond the column or columns that make up the PK)
>
> this is not good in your specific situation if the index is intended
> solely for the PK, especially if it is unique
>
> not sure if/when the behavior changed between versions, not sure how
> oracle tells the difference -- maybe someone else can shed light on
> that

i hope too, that someone can get light on my problem.

>
> related to this, 10g R2 SQL guide says:
>
> "You can also avoid rebuilding the index and eliminate redundant
> indexes by creating new primary key and unique constraints initially
> disabled. Then create (or use existing) nonunique indexes to enforce
> the constraint. Oracle does not drop a nonunique index when the
> constraint is disabled, so subsequent ENABLE operations are
> facilitated."
>

But i drop it and dont disalbe the Primary Key. I drop it to put the Primary Key on a other column.  

> however, i've obseved that the a pre-existing or seperately created
> unique index is also not dropped (in limited tests)
>
> ++ mcs

-- 
Received on Fri Feb 17 2006 - 08:41:25 CST

Original text of this message

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