SQL & PL/SQL

Hi,

Instead of ALTER TABLE test DROP CONSTRAINT xpk_test; statement you can submit the statement ALTER TABLE test DROP CONSTRAINT xpk_test CASCADE; it will work.

Regards,
Kamal Shrivastava

Or

Alter table test drop primary key;

Hope this helps

Hi,

yes both statements mentioned above
ALTER TABLE test DROP CONSTRAINT xpk_test CASCADE;
and
Alter table test drop primary key;

successfully remove the primary key, but only the pk. :-(
The index persists when these statements run on a database migrated from Oracle 9 to 10! So unfortunately no difference to the statement we used before.

TIA,
F.

Hi,

the following statement does what we were looking for:

ALTER TABLE test DROP PRIMARY KEY DROP INDEX;

Removes PK and Index and produces identical results on Oracle 9, Oracle 10 and databases that were migrated form 9 to 10!

Thanx for all hints,
F.

That is expected behaviour. If an index on the primary key columns already exists, then adding the primary key constraint does not add a new index. Later when you drop the primary key, index is not dropped since it was not created as part of the add primary key constraint statement.

On the other hand, if you do not have a prior index on the primary key columns, a index is created as part of the add constraint primary key statement. In this case, dropping the constraint will drop the iindex as well.

Since you did an export and import, the import works by creating the index separate and then adding the primary key constraint separate. Since in case of import, the two thiings happen separately, dropping the primary key now will NOT drop the index.

Look at the below scenario, here no export import is involved, but in first case index is not dropped automatically:

SQL> create unique index pk_emp1 on emp1(empno) ;
 
Index created.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
PK_EMP1                        EMP1                           UNIQUE
 
1 row selected.
 
SQL> alter table emp1 add constraint pk_emp1 primary key(empno) ;
 
Table altered.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
PK_EMP1                        EMP1                           UNIQUE
 
1 row selected.
 
SQL> alter table emp1 drop primary key ;
 
Table altered.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
PK_EMP1                        EMP1                           UNIQUE
 
1 row selected.
 
SQL>
SQL> drop index pk_emp1 ;
 
Index dropped.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
no rows selected
 
SQL> alter table emp1 add constraint pk_emp1 primary key(empno) ;
 
Table altered.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
PK_EMP1                        EMP1                           UNIQUE
 
1 row selected.
 
SQL> alter table emp1 drop primary key ;
 
Table altered.
 
SQL> select index_name, table_name, uniqueness from user_indexes where table_name = 'EMP1' ;
 
no rows selected
 
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

Dear All

All methods are fine, I don't know whether you have privileged to drop the index. I hope while you have created the table using table level. From oracle 9i we have one new feature creating index while we creating primary key.
(E.g) like
create table g1(a1 number(2) primary key using index(create index index_g1on g1(a1))); then if you follow earlier mentioned dropping procedure, your index will get dropped out .

Thanx for your confirmation and explanation of this behaviour. We overlooked that export/import causes this change due to the separate re-creation of the prio existing pk and index. Nevertheless we never had this problems when moving from oracle 8 to 9. So even though it's "expected behaviour" it looks like new or changed behaviour. ;-)

Thanx,
F.

Yes, there would be no problem, if all PKs where created during table creation, i.e. in one statement. But in our databases all PKs where created by separate ADD-CONSTRAINT-statements after the CREATE-TABLE-statement. For the future we can use the "one-statement-approach". But to avoid future problems in the already migrated databases -- where in other words the link between existing PKs and related indexes is broken -- the "...DROP PRIMARY KEY DROP INDEX"-approach is the only one working.

Thanx,
F.

I'm wondering why is there a need for you to drop the primary key on a table when moving from 9i to 10G?
Is it that the column list for the primary key is changing or that table no longer needs to have a primary key?
what happens to the existing application queries that were previously relying on this index?

Oh, that's a coincidence. There's no direct connection between the change of the PK and the migration. It was just bad luck: The PK was altered (i.e. dropped from one and re-created on another column) in the context of a table redesign during further development.

The long story: We develop standard-software which supports different databases, amongst others both Oracle 9 and 10 versions. The original PK was created years ago -- before even oracle 9 existed ;-) -- and now changed during a major redesign.

So we were very surprised that there is a difference in behaviour between versions 8/9 and 10. 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.

Very strange.. My database was not a export and import and i am on 9.2.0.7

This is what my test produced.

SQL> create table test(a number,b number);

Table created.

SQL> create unique index test_i on test(a,b);

Index created.

SQL> alter table test add constraint test_i primary key (a,b) using index tablespace user_data;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME
------------------------------
TEST_I

SQL> alter table test drop primary key;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

no rows selected

/[PRE]

G                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

Sorry.. i misread the post.

I concurr with Kamal

Don't mention it!

That's a good demonstration. Let's just do the same on Oracle 10.1.0.2.0. This time I did no export/import between any statement. Then it looks like this:

create table test(a number,b number);

Table created.

SQL> create unique index test_i on test(a,b);

Index created.

SQL> alter table test add constraint test_i primary key (a,b) using index tablespace users;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME                                                                      
------------------------------                                                  
TEST_I                                                                          

SQL> alter table test drop primary key;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME                                                                      
------------------------------                                                  
TEST_I                                                                          

SQL>

This demonstrates very clearly that there is a change in behaviour, isn't it. Any ideas how to switch back to the old behaviour?

TIA,
F.

Actually, I'm not sure you want to revert to the 9i behaviour. It seems to me to be wrong.

In both tests, you created a unique index on the columns, then later created a primary key, which used the existing index to enforce the key. In 9i, dropping the PK also dropped the pre-existing unique constraint. It was the dropping of the unique constaint that dropped the index, not directly the PK.

In 10g, Oracle maintained the pre-existing unique constraint even after you dropped the PK.

In 8i and 9i if you create the index without the UNIQUE, then it is maintained even when you drop the PK.

Hmm, yes and no. The last tests where only another demonstation of the general change in behaviour. Our core problem refers to indexes automatically created with PKs.

For indexes that existed before a PK maintaining is right, yes. I concur with that.

But the original discussion referred to indexes automatically created with PKs. And in my eyes it's not wrong to drop auto-indexes, when the PK that triggered them is removed. Particularly if these new behaviour changes in dependence of a export/import: The auto-index is removed as long as you don't perform any export/imports, but the auto-index is not removed as soon as you performed one export/import. Sorry, no offense, but if this is a correction, than it looks not very consistent. ;-) And no, the new behaviour seems not right to me.

Greetings,
F.

Hello,

As most of the third party softwares Toad etc. will generate this kind of code.
When you create a table with a primay key the code looks like this in SQL*Plus

<< SQL*Plus>>

create table test (a number, b number);

alter table test add constraint test_i primary key (a,b)
using index tablespace users;

The same code when you launch TOAD you will see unique index + primary key constraint. It is Toad's behaviour.

<< TOAD>>

create table test (a number, b number);

create unique index test_i on test(a, b);

alter table test add constraint test_i primary key (a,b)
using index tablespace users;

First thing to understand is when you create a primary key it will create a unique index by default with a not null constraint. You dont have to create a primary key constraint and again a unique index.

So if you go as per the TOAD's code and try to drop primary key you will still see the unique index. Which you have to manually drop it.

Just wondering why you are concerned about dropping Primary Key?

-Sri

Message was edited by:
Srikanth Pulikonda

Yes, why this happens the way it does, has been made clear. I understand why people see that the new behaviour is the right one when taking a closer look on the way constraints are handled. Still it's not consistent that behaviour changes due to export/import (see statements above!).

And besides of consistent behaviour we wish there would be a kind of backward compatibility to the "old" behaviour of versions 8 and 9. Right behaviour is one thing, compatibility is another. And other databases like e.g. PostgreSQL or MS SQL Server or MySQL or even Informix handle auto-indexes and PK as ORACLE Versions 8 and 9 did: Auto-drop them as soon the PK is removed.
Because we develop standard software and we support different DBMS and different versions of these DBMS, amongst others Oracle 9 and 10. No, not all of these mentioned above! ;-) Well, this change gives another annoying point more, where version-specific and/or DBMS-specific SQLs are needed. Before a ALTER TABLE ... DROP CONSTRAINT met most DBMS/versions. Just call me inflexible and stubborn. ;-)

But don't take this too serious that's just a side blow. I am aware that SQL is not the standard it should be and that there will always be DBMS-specifics. The main concern is the inconsistent behaviour!

Sorry giving you folks a hard time: Is there any way (server option whatever) to get this "wrong", but consistent behaviour of versions 8 and 9 back? ;-)

Thanks a lot to all of you for your help and especially your patience!
F.

Message was edited by:
user492066

Dear all,

I would also be very interested in knowing if there is a way to get back to the version 8 and 9 behaviour.

I'm experiencing the same problem with a third-party product for which I of course do not have the code. Even if the vendor is going to provide a fix, this would take time and it would be great if there were a workaround that we can apply ...