3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
erewien
  • 349
  • 4
  • 15
  • Maybe you can't do this, but it might be easier to just create new tables from scratch. – Tim Biegeleisen Aug 08 '17 at 11:33
  • I am creating the PART_ tables from scratch by `CREATE TABLE` statement. I can't drop the original tables beforehand, because I need the data contained inside. I just need to de-constraint them. – erewien Aug 08 '17 at 11:41
  • It might be that the partitioned table is still existing. If I'm correct both the partitioned and and the partition are somewhat related (only that the partitioned table is organized for greater DB performance)... Drop the constraint in the parent table and see if the problem remains... – Jason Krs Aug 08 '17 at 11:55
  • Try to query `all_objects` instead of constraints, Maybe you have another object (no constraint) with the same name. – Arkadiusz Łukasiewicz Aug 08 '17 at 12:00
  • 1
    Possible duplicate of [How to rename a primary key in Oracle such that it can be reused](https://stackoverflow.com/questions/6252355/how-to-rename-a-primary-key-in-oracle-such-that-it-can-be-reused) – markusk Aug 08 '17 at 12:01
  • I, unfortunately, cannot drop the constraint because there is a key dependency. This is a migration script and has to work on both non-partitioned tables and wrong-partitioned tables. In my case, the PK of MYTABLE is also partition key, so I cannot drop it just like that, that's why I am renaming the constraints in the first step. – erewien Aug 08 '17 at 12:01
  • Well yeah, it was the rogue index doing the trouble. But thanks for pointing out the all_objects table, might come in handy in the future. – erewien Aug 08 '17 at 12:17

1 Answers1

3

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";
markusk
  • 6,477
  • 34
  • 39