We started with a naive primary key:
SQL> CREATE TABLE books( title VARCHAR2(10), CONSTRAINT pk_title PRIMARY KEY(title)) ORGANIZATION INDEX; Table created.
Then realized it is not unique. Say, a book can be re-released under the same title:
SQL> ALTER TABLE books ADD(release_date DATE NOT NULL); Table altered.
Now to insert a re-released book, we have to relax the uniqueness on title somehow.
But I cannot drop and recreate the PK due to the table being index organized:
SQL> ALTER TABLE books DROP PRIMARY KEY; ALTER TABLE books DROP PRIMARY KEY * ERROR at line 1: ORA-25188: cannot drop/disable/defer the primary key constraint for index-organized tables or sorted hash cluster
I cannot manage to extend the existing key in-place either:
SQL> CREATE UNIQUE INDEX pk_title_date ON books(title, release_date); Index created. SQL> ALTER TABLE books MODIFY PRIMARY KEY USING INDEX pk_title_date; ALTER TABLE books MODIFY PRIMARY KEY USING INDEX pk_title_date * ERROR at line 1: ORA-14196: Specified index cannot be used to enforce the constraint.
What are my options ? Is it possible without moving data or recreating the table (the actual table is huge) ?