Modify Primary Key Using Index (Learning To Fly) February 27, 2014

One of the things I love about working with Oracle Database technologies is that there’s just so much one can learn. I make it an active goal of mine to try to learn something new at work each and every day, no matter how big or small. One of these days, I might blog about a presentation I put together a while ago on the common habits of highly successful DBAs (or technologists in general). One of these key habits I believe is the constant need to keep learning and to keep growing professionally.

One of the places I constantly turn to in order to learn something new is Jonathan Lewis’s Oracle Scratchpad blog. I doubt many folks who read my blog don’t 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” !!

I previously blogged about the new 12c capability of having Multiple Indexes On The Same Column List and demonstrated how it was now possible to “quickly” swap the index on say a Primary Key constraint by pre-building a new index (say a Unique index to replace an existing Non-Unique index), then drop/disable the constraint and old index, make the new index visible and re-applying the PK constraint.

Well, as Jonathan described, there’s an easier alternative that doesn’t require so much stuffing around with the PK constraint.

I’m just going to setup the same demo as I used in the initial multiple indexes discussion where I have a Non-Unique index policing the PK constraint of a table:

I then subsequently decide to replace the existing Non-Unique index policing the PK constraint with say a Unique Index instead. (Note the following also applies for Unique constraints as well). With 12c, I can pre-create another index with a different characteristic on the same column, I just have to initially make it Invisible:

Thereby changing the index policing the PK constraint without having to drop/disable the PK constraint in the process. I do have both indexes invisible for a brief period of time, so still exercise some caution, although the PK business rule is safe at all times without having to lock the table.

Obviously, if you wish to switch the index with one that uses a different column list (for example, if you wish to remove an unnecessary column from the policing index), then the indexes can simply be switched without having to alter their visibility attributes.

As Jonathan pointed out in his post, this capability dates back to later versions of 9i.