Enforcement of unique/primary key - drop index

Question

I am trying to drop an index :

DROP INDEX PK_CHARGES

but I get this error

cannot drop index used for enforcement of unique/primary key

Why I am getting this error? I will provide further information if you need any.

How to solve it?

Edit I have no primary key in the table, but I found this weird index that I don't remember I had added:

index name = SYS_C0040476 which have the same columns

Gormless

Because the index is used to enforce a primary key (I'm guessing, from the PK in the name), like the message says? Why are you trying to drop it? – Cribbage

Did you invesitigate for the enforcement of which unique/primary key the index is used? If so, what have you discovered? – Aghast

@AlexPoole I am trying to drop the index and I dont have a primary key – Gormless

You do have a primary key @Moudiz... that's what it says. – Kibbutznik

@ben from pl/sql I right click on the table, I click on the view, I go to the tab called 'keys' and its empty. – Gormless

Answer 1

You can query the ALL_CONSTRAINTS performance view to see which constraint the index is used by, and which table it applies to, e.g:

select owner, constraint_name, constraint_type,
    table_name, index_owner, index_name
from all_constraints
where index_name = 'PK_CHARGES';

I would expect the table name to be 'CHARGES', the constraint name to match the index name, and the constraint type to be 'P'. But since you have a table in mind, perhaps the names aren't following a helpful convention. Maybe an old version of the table was renamed, which would leave the constraints against the new name (e.g. CHARGES_BACKUP or something).

---

You said you click on the table, then on the view. Perhaps you're not looking at the table that the constraint/index is on; or perhaps you're looking at a view on top of the actual table. You also mention a SYS_ index on the same columns - which can't be on the same table. Do you have multiple similar tables, or access to multiple schemas? You shold run the above query for that index too. As mentions above, you might find an old version (or versions) of the table.

---

Once you've identified which table the constraint is on, you'll need to decide whether you should actually be keeping it, and if not you can remove it by dropping the constraint with an ALTER TABLE command.

Cribbage

Is it normal that I didnt find the name in my schema ? I mean i had a result of : owner:different schema , constrant name=pk_charges.... but in my schema I didnt find my constraint name – Gormless

btw yes all the types were P – Gormless

@Gormless - it depends on your environment. If you're in a dev environment where people have their own copies of objects then it's not unusual; but in that case you probably shouldn't be trying to drop the index/constraint in the first place, if it belongs to someone else. – Cribbage

thanx for your help alex I found the problem it was on another table , I was looking on the wrong table – Gormless

Answer 2

The problem with

But I found this weird index that I dont rember I hadve add

comes because you didn't add it. You had a primary key, then you dropped it, but when you do that Oracle doesn't drop the associated unique index that every primary key has.

So when you drop a primary key you have to drop the unique index of that primary key, that amazingly has the same name as the primary key had.

So for dropping a MY_TABLE_PK you must do:

ALTER TABLE MY_TABLE DROP PRIMARY KEY DROP INDEX;

so you ensure that the index is dropped as well.

Connacht

Answer 3

"from pl/sql I right click on the table"

The problem with IDEs is that they make us feel very productive, because we can do things with just a click instead of writing some code. This is a problem because when something unusual happens the IDE is no good for investigation and we lack the understanding of the underlying structure of the database which we need to help ourselves.

If you want to learn the Oracle database the worst thing you can do is download SQL Developer or PLSQL Developer or TOAD. They're all fine tools but the only people who should use them are the people who don't need to use them.

Percale

APC It doesnt mean if I am using the right click on table means I am 'learning oracle', as you meantioned IDE's can show things quickly, my problem and errors was showing from codes and querries. you comment is unpropriate because you dont know my background so you cannot judge me – Gormless

"If you want to learn [Oracle] the worst thing you can do is download SQL Developer..." etc. That's simply not true. The IDE gives you a huge leg up as a starting point - from knowing nothing to having the guidance of familiar interface principles to help you with the basics until you have a chance to develop a full understanding. I believe your comment looks like you're attempting to belittle anyway who starts out with anything other than a basic text editor for coding! – Briquette

Answer 4

the following worked for me with unique index:

ALTER INDEX UX_CHARGES UNUSABLE
/

DROP INDEX UX_CHARGES
/

see: https://docs.oracle.com/cd/E18283_01/server.112/e17120/indexes004.htm#insertedID3

Badinage