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

"A. Peters" <info_no_spam_at_ap-data.de> wrote in message news:ds4rld$5di$02$1_at_news.t-online.com...
>
> Hello!
> I´ve 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 doesn´t 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

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

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

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

++ mcs Received on Sun Feb 05 2006 - 07:21:00 CST