Can't drop Index on empty table - ORA-02429

[N]

I'm beginning work on an Oracle table that was created by someone
else. The table has not been used yet, and has no data in it. It has
a unique index on it which is a combination of 4 columns. I'd like to
remove one of the four and replace it with a different column.

I'm unable to alter the index, perhaps because of syntax issues. When
I tried to drop the index to start over, I got this message:

ORA-02429: cannot drop index used for enforcement of unique/primary
key

What can I do to get around this? Thanks.

[Fraser McCallum]

As the index you are trying to drop is the primary/unique key of the table
you will need to remove it with the alter table command. I believe Oracle
probably implement them in this way to stop you mistakenly dropping
unique/primary keys. You should probably read up on constraints in the
Oracle Documentation so you understand what you are dealing with.

Kind Regards

Fraser McCallum
MVP Oracle
www.brainbench.com

"N" <nick...@hotmail.com> wrote in message
news:77c198f0.02010...@posting.google.com...

[Sybrand Bakker]

alter table <table_name> drop primary key cascade;

or
alter table <table_name> drop constraint <constraint_name> cascade;

As the index enforces a constraint you need to drop the constraint not
the index. Dropping the constraint will drop the index.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address