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.

Answer 2

It depends on how unique index was created...below are the various ways and behaviour

1) first create unique index (on the column for which primary key to be defined) and then add the primary key constraint. In this situation your DDL to add the primary key will utilize the existing unique index. So when you drop the primary key it will not drop the index but only primary key. ==> this is your situation I guess...

2) While creating the table you define the primary key OR when you add the primary key when there was no existing unique index for the column(s) on which primary key to be defined, so system will create a unique index and use it for primary key. So in this case when you drop the primary key the unique index will also get dropped.