Oracle Scratchpad

February 1, 2014

Modify PK

Filed under: Indexing,Oracle — Jonathan Lewis @ 11:00 am GMT 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:

rem     Script:         modify_pk.sql
rem     Dated:          Jan 2014
rem     Author:         J.P.Lewis

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 I started working backwards, and it works down to (the earliest 9i I have access to). It doesn’t work on, and the 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.

Update Oct 2016

I was creating a little demo of this feature for a client when I realised that there was a side effect I had to test. My note says that drops the original index when you “move” the primary key – I had overlooked the fact that the way Oracle treats an index can vary with the way in which the index was created.

In the demo to the client I created the table and added the primary key without creating the index first, so Oracle had created the “primary key index” implicitly. When I got to the bit where I moved the primary key to a different index the implicitly created index was automatically dropped, even in 12c.

I’m currently checking for ways to avoid this drop (because I might want to keep the original PK index temporarily as an invisible index) – so there may be a future update.


I’d forgotten I’d written this note (and a related one), so I did a google search to see if I could find any decent notes about modifying the primary key. A search for “modify primary key” (in quotes) produced a reference to an article by Richard Foote – no surprises there, then, except his post started with a link to this note — which hadn’t shown up on the first page of the google results !


  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 GMT Feb 27,2014 | Reply

  2. […] an earlier posting I described how we can play games with primary key indexes in 12c because you can create multiple […]

    Pingback by Modify PK – 2 | Oracle Scratchpad — April 25, 2014 @ 5:49 pm BST Apr 25,2014 | Reply

  3. […] Having said that, this looks like an interesting option for those (possibly rare) occasions when you want to change a unique index into a non-unique index (for example, to change a unique constraint to deferrable). Rather than having to drop the index and create a new one – leaving the table unindexed while the index builds, you appear to have the option to: “create new index online”, “drop old index”. Moving a primary key constraint from one index to the other might not be so easy, of course, especially if there are foreign keys in place – but this certainly looks like a helpful step. [Update: actually it's easy to move the constraint – as I subsequently found in this post.] […]

    Pingback by Indexing 12c | Oracle Scratchpad — May 27, 2014 @ 2:48 pm BST May 27,2014 | Reply

  4. […] It’s inevitable that bits of this presentation will be out of date after 8 years. The first thing I noted that needed to be changed is the detail about changing the index that supports the priimary key. In the video I describe creating the new index then dropping a foreign key constraint, then the primary key, then recreating the primary key using the new index, then recreating the foreign key.  This is far more work than is needed, as discussed in a blog note I wrote 3 years later. […]

    Pingback by Video | Oracle Scratchpad — August 14, 2019 @ 12:21 pm BST Aug 14,2019 | Reply

  5. […] I came across this blog post from Richard Foote, which referenced another post from Jonathan Lewis.  It describes the following technique of modifying the constraint to use the […]

    Pingback by Adding covering fields to a Primary Key Index | — January 11, 2020 @ 2:51 am GMT Jan 11,2020 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: