Enforcement of unique/primary key - drop index

oracle primary-key unique-index
Moudiz picture Moudiz · Jul 15, 2013 · Viewed 26.3k times · Source

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

Alex Poole picture Alex Poole · Jul 15, 2013

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.

Related questions

Change Primary Key

I have a table in Oracle which has following schema: City_ID Name State Country BuildTime Time When I declared the table my primary key was both City_ID and the BuildTime, but now I want to change the primary …

Read More
UUID max character length

We are using UUID as primary key for out oracle DB, and trying to determine an appropriate max character length for the VARCHAR. Apparently this is 36 characters but we have noticed UUID'S generated which are longer than this - up …

Read More
Best way to reset an Oracle sequence to the next value in an existing column?

For some reason, people in the past have inserted data without using sequence.NEXTVAL. So when I go to use sequence.NEXTVAL in order to populate a table, I get a PK violation, since that number is already in use …

Read More