Why dropping a primary key is not dropping its unique index?

Question

1. I have a table with Col1 and Col2 as a composite primary key pk_composit_key and a unique index that was automatically created for the constraint.
2. I then altered the table to add new column Col3.

3. I dropped the pk_composit_key constraint:

   ALTER TABLE table_name DROP CONSTRAINT pk_composit_key;

4. Now, When I tried to insert records I got ORA-00001: unique constraint pk_composit_key violated.

   • Why am I getting that error?
   • When the key was dropped why wasn't the unique index dropped automatically?

Answer 1

You mentioned exporting and importing the schema, and if that happened in the environment that showed this behaviour it would explain what you're seeing; at least if you used legacy imp rather than the data pump impdp.

The original import documentation states the order objects are imported:

Table objects are imported as they are read from the export dump file. The dump file contains objects in the following order:

• Type definitions
• Table definitions
• Table data
• Table indexes
• Integrity constraints, views, procedures, and triggers
• Bitmap, function-based, and domain indexes

So the unique index would have been imported, then the constraint would have been created.

When you drop a primary key constraint:

• If the primary key was created using an existing index, then the index is not dropped.
• If the primary key was created using a system-generated index, then the index is dropped.

Because of the import order, the constraint is using an existing index,so the first bullet applies; and the index is retained when the constraint is dropped.

You can use the drop index clause to drop the index even if it wasn't created automatically:

ALTER TABLE table_name DROP CONSTRAINT pk_composit_key DROP INDEX;

See also My Oracle Support note 370633.1; and 1455492.1 suggests similar behaviour will occur with data pump import as well. I'm not aware of any way to check if an index is associated with a constraint at this level; there is no difference in the dba_constraints or dba_indexes views when you create the index manually or automatically. Including drop index will make it consistent though.