Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02429 / ORA-25188 dance (removing primary key and index)
ORA-02429 / ORA-25188 dance (removing primary key and index) [message #145179] Tue, 01 November 2005 10:36 Go to next message
arnematt
Messages: 24
Registered: November 2005
Location: Netherlands
Junior Member
Hi,

I am trying to remove a primary key from a table but it's used by and index. When I try to remove the index I get an error saying its needed for a primary key. LOL Laughing

CREATE TABLE temp1 (
VST_NR NUMBER (3) NOT NULL,
CONSTRAINT PK_temp1
PRIMARY KEY ( VST_NR))
ORGANIZATION INDEX NOCOMPRESS PCTTHRESHOLD 50;

this wont work:
SQL>alter table temp1 drop constraint pk_temp1;
alter table temp1 drop constraint pk_temp1
*
ERROR at line 1:
ORA-25188: cannot drop/disable/defer the primary key constraint for index-organized tables


-----------
BUT this wont work either:
SQL>drop index pk_temp1;
drop index pk_temp1
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key


-----------
Uhm ok... now what? Dropping the table is not an option.

Thanks a bunch

Arne

[Updated on: Tue, 01 November 2005 11:05]

Report message to a moderator

Re: ORA-02429 / ORA-25188 dance [message #145182 is a reply to message #145179] Tue, 01 November 2005 11:04 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You may not consider dropping the table an option, but that is the only path here - the index is the table.
Re: ORA-02429 / ORA-25188 dance [message #145184 is a reply to message #145182] Tue, 01 November 2005 11:07 Go to previous messageGo to next message
arnematt
Messages: 24
Registered: November 2005
Location: Netherlands
Junior Member
so you are saying it is not possible to change the way data is organized?

So the fastest option is to use a temp table to select it into, drop the table, recreate it again?

...
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #145186 is a reply to message #145179] Tue, 01 November 2005 11:24 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
If the table was not an IOT, you could drop the primary key.
but as it is an IOT, you are not allowed to drop PK.

Rajiv.
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #145194 is a reply to message #145179] Tue, 01 November 2005 11:52 Go to previous messageGo to next message
arnematt
Messages: 24
Registered: November 2005
Location: Netherlands
Junior Member

So, in other words, is it possible to change the organization of the table?
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #145196 is a reply to message #145194] Tue, 01 November 2005 11:58 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
why you needed the table to be an IOT First.
If the need was of an IOT, question should not have been arised
so as to drop the PK as it's a must when u create IOT.

Data is stored directly in index segment rather than data segment, so u are not allowed to drop PK.


Rajiv.
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #145205 is a reply to message #145196] Tue, 01 November 2005 13:07 Go to previous messageGo to next message
arnematt
Messages: 24
Registered: November 2005
Location: Netherlands
Junior Member
please read my post. I want to change the table organization and drop the key constraint.

Why:

Initially the need arised for index organized table for efficiency purposes.

However we have changed the approach - since this is a historic table there will be no UPDATES, only INSERTS. Hence we can no longer use the primary key since duplicates will be introduced.

We still need an efficient way to access the table but it should not imply a primary-key constraint. I am wondering if this is possible. And if it is possible to change the organization without dropping the table.

Hope this clears things up.
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #145207 is a reply to message #145205] Tue, 01 November 2005 13:22 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
You cannot mess with the PK here, in any way, shape, or form. You will have to drop this table and recreate it as a non-IOT.
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #145215 is a reply to message #145179] Tue, 01 November 2005 14:14 Go to previous messageGo to next message
arnematt
Messages: 24
Registered: November 2005
Location: Netherlands
Junior Member
ok i was already starting to fear that Smile

thanks
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #274455 is a reply to message #145215] Tue, 16 October 2007 02:14 Go to previous messageGo to next message
anupsoni
Messages: 7
Registered: October 2007
Junior Member
remove the constraints first and then drop or rebuild the index

alter table <<table_name>> drop constraints <<constraints_name>>;



Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #274458 is a reply to message #274455] Tue, 16 October 2007 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Entering the forum with an answer to a question that is 2 years is quite useless, don't you think?
In addition, OP didn't connect since 18 months, so I don't thinh he will see your answer.

Choose newer topics.

Regards
Michel
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #274459 is a reply to message #274458] Tue, 16 October 2007 02:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And you might want to think about reading the posts you reply to.

Answering a 2 year old post is pointless.
Answering a 2 year old post by suggesting something that the OP shows that he has tried and that has failed is just stupid.
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #274631 is a reply to message #274459] Tue, 16 October 2007 11:39 Go to previous messageGo to next message
anupsoni
Messages: 7
Registered: October 2007
Junior Member
JRowbottom

ARe you here to share the knowledge or criticise the people...

[Updated on: Tue, 16 October 2007 11:40]

Report message to a moderator

Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #274633 is a reply to message #274631] Tue, 16 October 2007 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You did it good when you removed your final insult.

Regards
Michel
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #274775 is a reply to message #274631] Wed, 17 October 2007 02:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oh dear - did I upset you by pointing out that your 'solution' not only wasn't going to work, but had been mentioned by the OP as having been tried unsuccessfully in the very first post of this thread?

If I felt any remorse about that, I'd say so, right here:


Nope - looks remorse free to me.

Next time, try reading the thread that you are posting on.
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #274945 is a reply to message #274775] Wed, 17 October 2007 16:49 Go to previous messageGo to next message
anupsoni
Messages: 7
Registered: October 2007
Junior Member
The first rule on the site is "Be Polite"

Being moderate doesn't give you any rights to insult the people..you should understand that..

FIRST you follow the rules and then expect others to do so..
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #274999 is a reply to message #274945] Thu, 18 October 2007 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think that if JRowbottom said the same thing for one of my post I didn't feel insult just put in the correct way.

Regards
Michel
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #275059 is a reply to message #274945] Thu, 18 October 2007 03:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be honest, I would value a technically correct but tactless answer substantially more than a polite but wrong answer.

Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #275142 is a reply to message #274999] Thu, 18 October 2007 08:48 Go to previous messageGo to next message
anupsoni
Messages: 7
Registered: October 2007
Junior Member
...May be I need to learn orafaq culture...but same things can be advised in more polite manner...that will be more effective
Re: ORA-02429 / ORA-25188 dance (removing primary key and index) [message #289967 is a reply to message #275142] Wed, 26 December 2007 23:01 Go to previous message
tthtlc
Messages: 1
Registered: December 2007
Junior Member
Dear Anupsoni,

Thank you for your post.

I tried the statement u mentioned, and it works on my HP-UX Oracle9i (92010). Wow....u saved my life, as I have taken many days to populate the table, and there is no way to waste time to recreate the table again. I just need to drop the index, but then dropping it result in ora-2429 errors, but then drop constraints worked!!

Thanks again, your belated and polite reply is what I treasured most in a community discussion. Technically correct or not is not important, most impt is best effort - if u have tried your best, be it wrong or not, I still appreciate it very much, because a person don't have many 100 seconds to live, if u can spent 100 secs on the answer, best effort, I appreciate it very much.


Previous Topic: ORA-01578: ORACLE data block corrupted
Next Topic: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Goto Forum:
  


Current Time: Fri Dec 02 14:34:12 CST 2016

Total time taken to generate the page: 0.20903 seconds