15

I need to modify an existing PK. Therefore I drop an recreate it.

ALTER TABLE B DROP CONSTRAINT PK_B;
ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY ("TYP", "NR", "HH", "QUART");

Unfortunately the last Statement will give me an error ORA-00955

If I create the PK constraint like it was defined originally with:

ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY ("TYP", "NR", "HH");

everything works fine.

My-Name-Is
  • 4,814
  • 10
  • 44
  • 84
  • 1
    Found this question having same error. In my case it was for embarrassing reason though - I had a `create index` command in script invoked from SQL*Plus and command was terminated by `;` followed by `/` on next line. The slash caused repeating command. Silly me but hope it may somebody help. – Tomáš Záluský Nov 07 '20 at 10:17

2 Answers2

27

Perhaps there is an INDEX associated with the PRIMARY KEY CONSTRAINT, and it is also named as PK_B.

You can check it as :

SELECT * FROM USER_INDEXES WHERE TABLE_NAME='<table_name>';

If that's true, then do :

ALTER INDEX "PK_B" RENAME TO "PK_XYZ";

Update : Regarding ALTER INDEX statement, few important points as mentioned by Justin in the comments

Oracle implicitly creates an UNIQUE index to support the PRIMARY KEY CONSTRAINT. Since, the index is of the same name that of the primary key, and now that the primary key is being modified, it is better to drop and re-create the index again as per the definition of the old primary key.

My conclusion :

  • The primary key constraint is enforced through a unique index.
  • If Oracle already finds an index – unique or non-unique – it uses it for the primary key.
  • If the index was initially created as non-unique, it will continue to show as non-unique, however it will actually be a unique index.

A good demonstration and quite detailed on other aspects too, by Arup : Primary Keys Guarantee Uniqueness? Think Again.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Be aware, though, that if there is an index with the same name as the primary key constraint, that likely implies that the index is unique and was created to support the old primary key constraint. Since the definition of the primary key constraint is being changed, there is a good chance that the index should be dropped or that it should be re-created as non-unique (depending on what the old primary key was). – Justin Cave Sep 23 '14 at 11:22
  • Completely agreed Justin. It reminds me of the several discussions on ***Do Primary Keys Guarantee Uniqueness?*** So, the index with same name was created implicitly to maintain the uniqueness and support the primary key constraint. I would edit my answer to add these points. – Lalit Kumar B Sep 23 '14 at 11:31
0

I had the same issue where I had to do the following to delete reference to a table from the view whilst recreating the database from the scratch. I was searching for the same in tables and indexes first.

connect sys/oracle as sysdba;
select * from all_tables
select * from all_indexes
(finally located the reference in the views)
select * from all_views where view_name like '%WKSTSTATE%';
drop view RUEGEN.WKSTSTATE;
donsasikumar
  • 31
  • 3