February 1, 2014

Modify PK

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:

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.

[…] 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.] […]