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

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.

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.