Oracle SQL primary key stuck
I am having a curious problem. I am making copies of existing tables and adding sections to them.
The process looks like this:
-
Rename the current constraints (cannot remove them without dropping the table because I will need the data later)
-
Create a new partitioned table that will structurally copy the current one. so I have
MYTABLE
(original) andPART_TABLE
(new partitioned) including FKs -
Copy data with offer
INSERT INTO SELECT
-
Change table with indices and PK
-
Rename the tables so I end up with
MYTABLE
(new section) andTRASH_TABLE
(original)
In step 4, unfortunately, I got the 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 in use by an existing object"
Now I logically assumed that I just forgot to rename the PK, so I check TRASH_TABLE
, but I see the correctly renamed PK there.
I also ran
SELECT *
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_NAME LIKE 'PK_MYTABLE'
and it returned 0 results. It's the same with the table USER_CONSTRAINTS
.
Renamed PCs are displayed correctly.
Another thing I noticed is that only PCs are locked this way. Adding FK or UNIQUE constraints works fine.
source to share
As stated in How to rename a primary key in Oracle so that it can be reused , the problem is that Oracle is creating an index on the primary key. You also need to rename the autogenerated index. As suggested by Tony Andrews , try
ALTER INDEX "PK_MYTABLE" RENAME TO "PK_MYTABLE_OLD";
source to share