| ORA-02429 / ORA-25188 dance (removing primary key and index) [message #145179] |
Tue, 01 November 2005 10:36  |
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 
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 #145184 is a reply to message #145182] |
Tue, 01 November 2005 11:07  |
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 #145205 is a reply to message #145196] |
Tue, 01 November 2005 13:07 |
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 #274775 is a reply to message #274631] |
Wed, 17 October 2007 02:07 |
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 #289967 is a reply to message #275142] |
Wed, 26 December 2007 23:01 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
