Other Oracle database topics - Problem with dropping a Primary Key and his Index on Oracle 10R1

Author

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

A. Peters

2006-02-05, 7:23 am

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

Mark C. Stock

2006-02-05, 7:23 am

"A. Peters" <info_no_spam@ap-data.de> wrote in message
news:ds4rld$5di$02$1
@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

A. Peters

2006-02-17, 9:23 am

Mark C. Stock wrote:

>
> "A. Peters" <info_no_spam@ap-data.de> wrote in message
> news:ds4rld$5di$02$1
@news.t-online.com...
>
> 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

--

DA Morgan

2006-02-17, 9:23 am

A. Peters wrote:

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

If it is possible to do this it seems highly liklely your design is
flawed: Two different columns each, independently, capable of uniquely
defining a unique record?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Sponsored Links