The primary key that wouldn't die.....

Here's a little obscurity which came up again last week about how sometimes dropping a primary key doesn't actually remove a primary key (well it does, but it doesn't - you'll see what i mean in a bit).

First up lets create a simple test case to illustrate the point

Lets create a basic user


SYS@DB>create user pkdemo identified by pkdemo;

User created.

SYS@DB>grant connect,resource to pkdemo;

Grant succeeded.

SYS@DB>alter user pkdemo quota unlimited on users;

User altered.

SYS@DB>alter user pkdemo default tablespace users;

User altered.


SYS@DB>conn pkdemo/pkdemo;
Connected.

Now lets logon as that user and create a simple table with a simple primary key

SYS@DB>conn pkdemo/pkdemo
Connected.
PKDEMO@DB>create table tab1(col1 number, col2 date);

Table created.

PKDEMO@DB>alter table tab1 add primary key (col1);

Table altered.

PKDEMO@DB>insert into tab1 values (1,sysdate);

1 row created.

PKDEMO@DB>insert into tab1 values (1,sysdate);
insert into tab1 values (1,sysdate)
*
ERROR at line 1:
ORA-00001: unique constraint (PKDEMO.SYS_C0087800) violated


So the contraint works just fine and prevents me entering the data - now we drop the primary key and see what happens

PKDEMO@DB>alter table tab1 drop primary key;

Table altered.

PKDEMO@DB>insert into tab1 values (1,sysdate);

1 row created.

PKDEMO@DB>select * from tab1;

      COL1 COL2
---------- ---------
         1 28-APR-14
         1 28-APR-14

And as we would expect we can now insert duplicates - no surprises there. But hang on there is more than one way to create a PK - what if i create it on a prebuilt index? What happens then?


PKDEMO@DB>create table tab2(col1 number, col2 date);

Table created.

PKDEMO@DB>create unique index demoidx on tab2(col1);

Index created.

PKDEMO@DB>alter table tab2 add primary key (col1) using index demoidx;

Table altered.

PKDEMO@DB>insert into tab2 values (1,sysdate);

1 row created.

PKDEMO@DB>insert into tab2 values (1,sysdate);
insert into tab2 values (1,sysdate)
*
ERROR at line 1:
ORA-00001: unique constraint (PKDEMO.SYS_C0087801) violated

So same as before blocked as we expect - now lets drop the PK.

PKDEMO@DB>alter table tab2 drop  primary key ;

Table altered.

PKDEMO@DB>insert into tab2 values (1,sysdate);
insert into tab2 values (1,sysdate)
*
ERROR at line 1:
ORA-00001: unique constraint (PKDEMO.DEMOIDX) violated

And there's the surprise, dropping the PK leaves the unique index in place and so effectively enforces the PK restriction still. So in the case where you added a PK using a prebuilt index you'll hit this situation.



So how to really drop the PK? Fine the index name then drop manually? Shout at the screen until it drops?

You can actually do it in one command - let me demonstrate by putting the PK back on again:

PKDEMO@EIANCAPP>alter table tab2 add primary key (col1) using index demoidx;

Table altered.

PKDEMO@EIANCAPP>alter table tab2 drop  primary key drop index;

Table altered.

So the key part is the additional 'drop index' clause after the drop primary key.



The default of leaving the index in place if it's a prebuilt one seems sound i think, Oracle assume that this index has a purpose outside of it being used to enforce a PK constraint. It creates the odd nasty surprise though when you forget about this behaviour!