| DROP PRIMARY KEY doesn't drop the UNIQUE INDEX (migrated db oracle 9 to 10) [message #161340] |
Fri, 03 March 2006 06:27  |
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;
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  |
 |
Mahesh Rajendran
Messages: 10708
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 #161630 is a reply to message #161363] |
Mon, 06 March 2006 06:50 |
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. 
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 |
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 |
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! 
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 #329923 is a reply to message #161340] |
Fri, 27 June 2008 01:30 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
