Oracle SQL primary key stuck

Question

I ran into a curious problem. I am creating copies of currently existing tables and adding partitions to them.

The process is as follows:

1. Rename current constraints (can't drop them without dropping table itself because I will need data later)

2. Create a new partitioned table that structurally copies current one. so I have MYTABLE (original) and PART_TABLE (new partitioned), including FKs

3. Copy data with INSERT INTO SELECT clause

4. Alter table with indexes and PKs

5. Rename tables so I end up with MYTABLE (new partitioned) and TRASH_TABLE (original)

In step 4 unfortunately, I got an error

ALTER TABLE MYTABLE ADD CONSTRAINT "PK_MYTABLE"
    PRIMARY KEY ("MY_ID", "SEQUENCE")
    USING INDEX LOCAL TABLESPACE INDEXSPACE;

SQL Error: ORA-00955: "name is already used by an existing object"

Now, I logically assumed that I simply forgot to rename the PK so I check TRASH_TABLE, but I see there the correctly renamed PK.

I also ran

SELECT *
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_NAME LIKE 'PK_MYTABLE'

and it returned 0 results. Same with table USER_CONSTRAINTS.

Renamed PKs are displaying correctly.

Another thing I noticed is that only PKs are locked this way. Adding FKs or UNIQUE constraints work just fine.

Answer 1

As stated in How to rename a primary key in Oracle such that it can be reused, the problem is that Oracle creates an index for the primary key. You need to rename the autogenerated index as well. As suggested by Tony Andrews, try

ALTER INDEX "PK_MYTABLE" RENAME TO "PK_MYTABLE_OLD";