Home » SQL & PL/SQL » SQL & PL/SQL » DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10)
DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #161340] Fri, 03 March 2006 06:27 Go to next message
F.
Messages: 6
Registered: February 2004
Junior Member
Hello!

We have a problem with an auto-index from a primary key (PK), which is not dropped when removing the PK. When creating PKs, oracle automatically adds a unique index to hold the primary key rule. When the pk is dropped the index is dropped too. This works fine as long as both statements (create and drop) are performed on either Oracle 9 or Oracle 10, i.e. both statements run on the same version.

If the database is transferred from Oracle 9 to 10 between both statements the index persists.

To reproduce:
- Create a primary key under Oracle 9 and export the Database
- Import it in Oracle 10
- Drop the primary key

Result:
Oracle 10 does not drop the unique Index created with the primary key!

Sample SQL:
-- run this on oracle 9 --
CREATE TABLE test ( pk INTEGER );
ALTER TABLE test ADD CONSTRAINT xpk_test PRIMARY KEY ( pk );

-- make an dump with exp and import it on Oracle 10
-- run this on oracle 10 --
ALTER TABLE test DROP CONSTRAINT xpk_test;
-- now only the primary key xpk_test is dropped, the index xpk_test stays

Alternative approaches like
ALTER TABLE test DROP CONSTRAINT xpk_test CASCADE; 
--OR
Alter table test drop primary key;
give no difference.

Why?
How to we avoid this behaviour? Is there a special method to deal with old (migrated) primary keys/indexes?

Any hint would be appreciated!

TIA,
F.
---

Environment
Oracle 9.2/Win2k
Oracle 10.1/Win2k
Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #161363 is a reply to message #161340] Fri, 03 March 2006 08:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10626
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There are subtle differences, that matter a lot. in 10g,
SQL> create table test (pk integer);

Table created.

SQL> alter table test add constraint xpk_test primary key( pk);

Table altered.

SQL> alter table test drop primary key;

Table altered.

SQL> select index_name from user_indexes;

no rows selected


This a good feature.
You can look into ddl ( created by indexfile option or trace from export/import)
1
     2  REM  CREATE TABLE "SCOTT"."TEST" ("PK" NUMBER(*,0)) PCTFREE 10 PCTUSED 40
     3  REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
     4  REM  GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
     5  REM  ... 0 rows
     6  CONNECT SCOTT;
     7  CREATE UNIQUE INDEX "SCOTT"."XPK_TEST" ON "TEST" ("PK" ) PCTFREE 10
     8  INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS
     9  1) TABLESPACE "USERS" LOGGING ;
    10  REM  ALTER TABLE "SCOTT"."TEST" ADD CONSTRAINT "XPK_TEST" PRIMARY KEY
    11  REM  ("PK") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
    12  REM  65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING
    13  REM  ENABLE ;

You can see with export/import, first unique index is created and then a primary key added and makes use of the index present already.
During import, the samething is repeated.
So when your drop the constrant, only constraint is dropped.
This cannot be experienced with 9i , which did create a few painful aftermath in a certain remote cases.
Please look into documentation. There are more options documented and explained.

[Updated on: Fri, 03 March 2006 08:47]

Report message to a moderator

Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #161375 is a reply to message #161363] Fri, 03 March 2006 10:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10626
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
So the base reason is the the table creation syntax.

>>CREATE TABLE test ( pk INTEGER );
>>ALTER TABLE test ADD CONSTRAINT xpk_test PRIMARY KEY ( pk );
try this

sql> create table test (pk integer primary key).
the ddl that import session will use is

REM  CREATE TABLE "SCOTT"."TEST" ("PK" NUMBER(*,0)) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
REM  GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM  ... 0 rows
REM  ALTER TABLE "SCOTT"."TEST" ADD PRIMARY KEY ("PK") USING INDEX PCTFREE
REM  10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
REM  GROUPS 1) TABLESPACE "USERS" LOGGING ENABLE ;
Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #161425 is a reply to message #161375] Sat, 04 March 2006 00:33 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
ALTER TABLE test DROP PRIMARY KEY DROP INDEX;


If you want to guarantee functionality, always use the DROP/KEEP INDEX clause.
_____________
Ross Leishman
Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #161630 is a reply to message #161363] Mon, 06 March 2006 06:50 Go to previous messageGo to next message
F.
Messages: 6
Registered: February 2004
Junior Member
Mahesh Rajendran wrote on Fri, 03 March 2006 08:24

There are subtle differences, that matter a lot. in 10g,
[...]
SQL> alter table test drop primary key;
Table altered.
SQL> select index_name from user_indexes;
no rows selected



Not exactly, your log displays the behaviour when using either homogeneous oracle9 or homogeneous oracle 10. When migrating the database between CREATE and DROP it's like this:
--using Oracle9--
SQL> create table test (pk integer);
Table created.
SQL> alter table test add constraint xpk_test primary key( pk);
Table altered.
--dump on Oracle9--

--!!!import dump on Oracle10!!!---
SQL> alter table test drop primary key;
Table altered.
SQL> select index_name from user_indexes;

INDEX_NAME
------------------------------
XPK_TEST

1 row


Mahesh Rajendran wrote on Fri, 03 March 2006 08:24


This a good feature.
You can look into ddl ( created by indexfile option or trace from export/import)
[...]
You can see with export/import, first unique index is created and then a primary key added and makes use of the index present already.
During import, the samething is repeated.
So when your drop the constrant, only constraint is dropped.
This cannot be experienced with 9i , which did create a few painful aftermath in a certain remote cases.
[...]

Okay, this explains the different behaviour between versions. Thanx a lot for confirming that there IS a change in behaviour.

Mahesh Rajendran wrote on Fri, 03 March 2006 10:46


try this

sql> create table test (pk integer primary key).
the ddl that import session will use is


Yes, this would solve the problem for future use. But the problem already exists in different migrated databases. Cool

Greetings,
F.

[Updated on: Mon, 06 March 2006 06:56]

Report message to a moderator

Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #161632 is a reply to message #161425] Mon, 06 March 2006 06:54 Go to previous messageGo to next message
F.
Messages: 6
Registered: February 2004
Junior Member
rleishman wrote on Sat, 04 March 2006 00:33

ALTER TABLE test DROP PRIMARY KEY DROP INDEX;


If you want to guarantee functionality, always use the DROP/KEEP INDEX clause.
_____________
Ross Leishman


That's what we were looking for! Thank you! That's a syntax which runs on oracle 9 and 10 and gives similar and even more important tidy results on both versions. Great! Thanx again!
Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #161778 is a reply to message #161632] Tue, 07 March 2006 02:59 Go to previous messageGo to next message
F.
Messages: 6
Registered: February 2004
Junior Member
Addendum:
Just discovered that this is not just a migration problem!

When you create a PK in versions 8 or 9, make different exports/imports and then drop the PK, the index is gone too. Doing the same in Oracle 10, the index persists after one export/import, even without a migration!

Well this might be new expected behaviour, when taking a closer look an the obviously new way this constraints are handled during export/import. But since this was not the behaviour of versions 8 and 9, in our eyes this is more a misbehaviour, or in other words a bug, than a new feature! Sad

Is there any way -- e.g. a server option or something -- to get the old behaviour back?

TIA,
F.
Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #329902 is a reply to message #161778] Fri, 27 June 2008 00:41 Go to previous messageGo to next message
win2333
Messages: 18
Registered: December 2005
Location: Philippines
Junior Member

Hi,

I've also encountered this problem. Can anyone give me the solution? By the way, I'm exporting the table from Oracle 9i to Oracle 10.2.0.3.0.

Jun
Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #329911 is a reply to message #329902] Fri, 27 June 2008 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can anyone give me the solution?

rleishman
ALTER TABLE test DROP PRIMARY KEY DROP INDEX;

Is this not a solution?

Regards
Michel
Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #329923 is a reply to message #161340] Fri, 27 June 2008 01:30 Go to previous messageGo to next message
F.
Messages: 6
Registered: February 2004
Junior Member
Hi,

there's no other solution as to always (manually) secure the corresponding index of an PK CONSTRAINT is removed too.

So use "ALTER TABLE test DROP PRIMARY KEY DROP INDEX;" as mentioned before or write a procedure for dropping PKs that always drops a remaining index if necessary.

We used the last option for full compatibility between different version's as we have to support customer databases using ORA versions 9, 10 and 11.

Hth,
F.
Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #330251 is a reply to message #329923] Sat, 28 June 2008 04:04 Go to previous messageGo to next message
win2333
Messages: 18
Registered: December 2005
Location: Philippines
Junior Member

Hi,

Thanks for the advice. I already did that and it works. I have misspelled some code.

Thanks,
JUn
Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #537877 is a reply to message #161340] Mon, 02 January 2012 05:39 Go to previous message
sachin2.kumar
Messages: 1
Registered: January 2012
Location: Mumbai
Junior Member
Perfect answer to this topic:
ALTER TABLE <Schema>.<TableName> DROP PRIMARY KEY DROP INDEX;
Previous Topic: Oracle PL/SQL
Next Topic: Hierarchical Query
Goto Forum:
  


Current Time: Sat Sep 20 17:38:46 CDT 2014

Total time taken to generate the page: 0.10350 seconds