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 22.214.171.124 I started working backwards, and it works down to 126.96.36.199 (the earlist 9i I have access to). It doesn’t work on 188.8.131.52, and the 184.108.40.206 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 220.127.116.11 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 my demo I created the table and added the primary key, which implicitly created the “primary key index”. When I got to the big where I moved the primary key to a different index the original 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 expect 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 !