1 2 Previous Next 18 Replies Latest reply: Jul 31, 2006 2:44 PM by 525856 RSS

    DROP PRIMARY KEY doesn't drop the UNIQUE INDEX

    495069
      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

      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.
        • 1. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
          472969
          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
          • 2. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
            USER101
            Or

            Alter table test drop primary key;

            Hope this helps
            • 3. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
              495069
              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.
              • 4. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
                495069
                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.
                • 5. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
                  27876
                  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>
                  • 6. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
                    448776
                    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 .
                    • 7. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
                      495069
                      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
                      index 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.
                      [...]
                      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.
                      • 8. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
                        495069
                        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 .
                        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.
                        • 9. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
                          27876
                          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?
                          • 10. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
                            495069
                            I'm wondering why is there a need for you to drop the
                            primary key on a table when moving from 9i to 10G?
                            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.
                            • 11. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
                              USER101
                              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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                              • 12. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
                                USER101
                                Sorry.. i misread the post.

                                I concurr with Kamal
                                • 13. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
                                  495069
                                  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.
                                  • 14. Re: DROP PRIMARY KEY doesn't drop the UNIQUE INDEX
                                    John Spencer
                                    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.
                                    1 2 Previous Next