Tuesday, October 9, 2007

ORA -2429:Cannot Drop Index used for enforcement of Unique/primary key

To simulate the error do the following:

create table temp_index as select object_name from user_objects where object_type='INDEX';

create table testing(testid number primary key, testchar varchar2(200));

Find out the index created for the primary key by the following statement:

select object_name from user_objects where object_type='INDEX'
minus
select object_name from temp_index;

Drop the index by issuing the following statement:
drop index SYS_C00249876;

You will get the error ORA-02429: Cannot drop index used for enforcement of unique/primary key

In Index organized tables this problem will be more. You will not be able to drop either the primary key or index.

Consider the example:
CREATE TABLE temp1 (
testid NUMBER (3) NOT NULL,
CONSTRAINT pk_temp1
PRIMARY KEY ( testid))
ORGANIZATION INDEX ;

alter table temp1 drop constraint pk_temp1;

You will get the following error:
ORA-25188: Cannot drop/disable/defer the primary key constraint for index-organized tables or sorted hash cluster

drop index pk_temp1;
You will get the following error:
ORA-02429: Cannot drop index used for enforcement of unique/primary key

7 comments:

Anonymous said...

select CONSTRAINT_TYPE,CONSTRAINT_NAME from user_constraints where INDEX_NAME = '.

alter table drop constraint instead of drop index (if index is PK)

dong dong23 said...

retro jordans
cheap jordans
polo ralph lauren
longchamp outlet
gucci handbags
oakley sunglasses
ralph lauren
louis vuitton bags
louis vuitton handbags
toms outlet
cheap jerseys
adidas stan smith
supra shoes
louis vuitton outlet
tods outlet
hollister uk
michael kors outlet
louis vuitton outlet
oakley sunglasses
replica watches
tory burch sale
kate spade handbags
michael kors outlet online
nike uk
louis vuitton
giuseppe zanotti
fitflop sandals
michael kors outlet online
gucci handbags
kate spade handbags
hollister clothing
ray bans
coach factory outlet
nike roshe run
celine
jordans for sale
celine
hollister clothing
marc jacobs
christian louboutin shoes
20166.4wengdongdong

raybanoutlet001 said...

fitflops
cleveland cavaliers jerseys
ugg boots
ugg outlet
louboutin shoes
true religion outlet
ray ban sunglasses
ugg boots
houston texans jerseys
new balance shoes

aaa kitty20101122 said...

michael kors handbags
curry shoes
cheap jordans
asics shoes
links of london sale
ferragamo belts
adidas ultra boost
adidas ultra boost uncaged
michael kors purses
nike air force

郑佳颖 said...

air max 95
light up shoes
nike huarache
dior sunglasses
yeezy boost 350
nmd r1
yeezy boost 350 v2
michael kors outlet
air max
ferragamo sale

jeje said...

nfl jerseys
kobe 9
nike air max
pandora charms
ferragamo sale
nike air force 1 high
kyrie 4
harden shoes
nike huarache
adidas outlet

jeje said...

moncler
harden shoes
kate spade outlet
kobe 11
yeezy
vans outlet
nike air max 90
adidas yeezy boost
true religion jeans
hermes belt