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) and PART_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) and TRASH_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.

+3


source to share


1 answer


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";

      

+3


source







All Articles