Oracle SQL primary key stuck

Viewed 283

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.

1 Answers
Related