6

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) ?

3
  • 1
    I think you're out of luck there (I don't think even dbms_redef can do this), but would love to be proven wrong. (The "alter table primary key" trick has no chance of working on an IOT, it's not an in-place modification at all for an IOT.)
    – Mat
    Nov 9 '15 at 17:06
  • "naive" primary key? roflmao (I know it's a typo - still can't help chuckling at the thought ) (I wonder if it's any different than a non-intelligent key? O.o )
    – Ditto
    Nov 9 '15 at 17:08
  • 1
    It's not a typo but 'm not English speaker so it could be wrong. I meant that assumptions were made about uniqueness, that turned out to be wrong (after a long time and the need for new features).
    – Gnurfos
    Nov 10 '15 at 8:47
2

An index-organized table (IOT) is just that; an index with no "real" table. All data besides the primary key are just "tacked on" to the primary key, so for example if you have an IOT with 6 columns, 2 of which make up the primary key, behind the scenes you just have an index with 6 columns, of which the first 2 columns make it unique.

So, sorry, only way to fix it is to recreate the table; to improve the rebuild time temporarily disable logging. You cannot use an append hint on inserts to an IOT table to force a direct path insert:

SQL> CREATE TABLE books_new (
    title VARCHAR2(10),
    release_date DATE NOT NULL,
    CONSTRAINT pk_title PRIMARY KEY(title,release_date))
  ORGANIZATION INDEX NOLOGGING;

SQL> INSERT INTO books_new 
     (SELECT title, '01-Jan-1980' FROM books);
SQL> ALTER TABLE books_new LOGGING;
SQL> ALTER TABLE books RENAME TO books_old;
SQL> ALTER TABLE books_new RENAME TO books;
2
  • 4
    '01-Jan-1980' is a character literal, not a date and relies on implicit data type conversion to the DATE data type. Don't do that. Use explicit date literals. Either to_date('01-Jan-1980', 'dd-mon-yyyy') which is subject to the client's NLS settings. Or better a standard ANSI SQL literal: date '1980-01-01' Nov 9 '15 at 19:10
  • Yeah, I was being lazy. Glad you made that clear for people who don't know better. :) Nov 9 '15 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.