How to rename a primary key in Oracle such that it can be reused

Question

On Oracle, I create a table like this:

CREATE TABLE "Mig1"(
  "Id" INTEGER  NOT NULL
  , CONSTRAINT "PK_Mig1" PRIMARY KEY 
(
   "Id"  ) 
) 

Then, I rename the PK:

ALTER TABLE "Mig1" RENAME CONSTRAINT "PK_Mig1" TO "PK_XXX"

Then, I rename the table:

ALTER TABLE "Mig1" RENAME TO "XXX"

Then, I try to create another table that uses the name of the previously renamed table:

CREATE TABLE "Mig1"(
  "Id" INTEGER  NOT NULL
  , CONSTRAINT "PK_Mig1" PRIMARY KEY 
(
   "Id"  ) 
) 

At this point I get: An error occurred: ORA-00955: name is already used by an existing object. And this is because somehow the primary key of the first table is still around in some way although it was renamed. If I try to create the second table like this:

CREATE TABLE "Mig1"(
  "Id" INTEGER  NOT NULL
  , CONSTRAINT "YYY" PRIMARY KEY 
(
   "Id"  ) 
) 

it works. So how do I rename the primary key correctly with all of its associated resources such that its name can be reused?

Shalna

Answer 1

There is an index associated with the primary key constraint, and it is probably still called "PK_Mig1". Try this:

ALTER INDEX "PK_Mig1" RENAME TO "PK_XXX";

Plump

Works like a charm. Sorry, did not know that Oracle is maintaining an extra index besides the constraint (coming from the SQL Server world). – Shalna

Oracle creates (or hijacks) an index in all cases to maintain primary key and unique constraints. Otherwise, how else could you do it? – Pero