Oracle Scratchpad

February 1, 2014

Modify PK

Filed under: Indexing,Oracle — Jonathan Lewis @ 11:00 am BST Feb 1,2014

Sitting in the lounge waiting to be called for my flight I was musing on the 12c feature of having multiple indexes defined on the same ordered column set  when a thought crossed my mind and I decided to run a little test that looked like this:

create table t1 as select * from all_objects where rownum <= 10000;

create unique index t1_pk on t1(object_id);

alter table t1 add constraint t1_pk primary key(object_id);

create index t1_i1 on t1(object_id, object_name);

drop index t1_pk;

/*
    expect ORA-02429: cannot drop index used for enforcement of unique/primary key
*/

alter table t1 modify primary key using index t1_i1;

drop index t1_pk;

For years I’ve been assuming that you really have to mess around with the PK (and any related FKs) if you want to change the index supporting the primary key – but this code demonstrates that you can add a new index to a table and “move” the primary key to it before dropping the original index.

The worrying thing about this (for me, at any rate) is that it isn’t a new feature – after testing it on 11.2.0.4 I started working backwards, and it works down to 9.2.0.8 (the earlist 9i I have access to). It doesn’t work on 8.1.7.4, and the 9.2.0.8 version behaves slightly differently from later versions because the original PK index disappears as the constraint is moved.

As I’ve often said about trust – keep an eye on the date and version of any article you read, it may no longer be true.

1 Comment »

  1. […] already know what a fantastic source of information this is. Reading a recent posting of his on Modifying Primary Keys was one such moment where I went “wow, I didn’t know you could do that” […]

    Pingback by Modify Primary Key Using Index (Learning To Fly) | Richard Foote's Oracle Blog — February 27, 2014 @ 6:34 am BST Feb 27,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,507 other followers