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.
If you have created a database object such as a table, view, index, or synonym that already exists you will receive this error. The database objects must have distinct names. You need to have a unique name for the database object or modify or drop the existing object so it can be reused.
Use the DROP TYPE statement to drop the specification and body of an object type, a varray, or a nested table type. See Also: DROP TYPE BODY for information on dropping just the body of an object type. CREATE TYPE and ALTER TYPE for information on creating and modifying types.
You may be seeing the Ora-00942 error because you are referencing a table or view in a schema which you did not create but one that is in another schema. To correctly execute the query from another schema, you must reference the table by the schema name.
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 :
A good demonstration and quite detailed on other aspects too, by Arup : Primary Keys Guarantee Uniqueness? Think Again.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With