Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Venkat.

Asked: December 30, 2000 - 12:03 am UTC

Last updated: February 06, 2009 - 3:29 pm UTC

Version: 7.3.2

Viewed 10K+ times! This question is

You Asked

Is there a way to drop primary key constraint without dropping the associated Unique Index?

We are facing this situation in a Data Warehouse installation. We had used disable/ enable constraints as a way to trap invalid records. We had done this as a general rule for all type of validations. As the size of the database grew, we are having performance issues. After reviewing the design, we have identified a set of primary keys which are guaranteed in terms of data cleansing, hence we want to remove them. We don't want to drop the index,since the index could be used in any query/ ETL scenario. We can always drop the constraint and create another index - we wanted to avoid this step if possible since creating index also takes quite a bit of time and we want to implement this with minimal end user impact.


and Tom said...

If the data is "gauranteed" then having the constraint + the unique index is not more or less performant then just having the index.

In this case, there would be NOTHING gained by not having the constraint but having the index. Don't disable these constraints that are "gauranteed" -- just leave them be and things will be as if the constraint did not exist. A primary key constraint in 7.3 is simply a UNIQUE INDEX + NOT NULL constraint -- nothing more and nothing less. If you left the index but got rid of the constraint, our code path would be identical, same amount of work!

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Drop and recreate primary key constraint

martina, August 30, 2007 - 10:17 am UTC

Hello Tom,

We have a maintenance routine which exchanges the oldest partition of some partitioned table with a normal one.

I am saying "alter Table PT exchange partition x with table T including indexes". To be able to do this I am copying the Primary key from table PT. (In fact i'm copying the index and then create a conforming primary key constraint).

now one of the tables is "mixed up", it was not created properly. the index was created like a,b,c and the pk constraint was created a,c,b. This was not done on purpose. So what i would like to do would be drop the constraint but keep the index. It is a big table. As this is a mistake by the creator of the table (probably me) it is not worth it to redesign the maintenance routine (mine as well).

The DB is Version 9.2.0.8

While i wrote this post the create index on a test-DB same size has finished within 30min. But i'm still curious if it is possible to drop a constraint without dropping the index.

thanks,
martina
Tom Kyte
September 04, 2007 - 5:13 pm UTC

ops$tkyte%ORA9IR2> create table t( a int, b int, c int, d int );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create index t_idx on t(a,b,c);

Index created.

ops$tkyte%ORA9IR2> alter table t add constraint t_pk primary key(a,c,b);

Table altered.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
T_IDX

ops$tkyte%ORA9IR2> alter table t drop constraint t_pk KEEP INDEX;

Table altered.

ops$tkyte%ORA9IR2> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
T_IDX

Sorry, i forgot

martina, August 30, 2007 - 10:31 am UTC

When attempting to exchange we got of course:

-14130/ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION

martina

shame on me!

martina, September 05, 2007 - 2:48 am UTC


Primary key issue on oracle 10g

Raj, February 09, 2008 - 6:30 am UTC

Hi Tom,
  Finally got something which amazed me this week on primary key.

We have a huge table on which we create a partition monthly and load data through sql loader. The indexes are local partitioned .We have a primary key constraint on the table.

So as the index was in unusable status, we do a drop of the primary key(normal process).When we tried to create the unique index, it failed with the error saying it already exits.I also received the same error on a non partitioned table too.

Simulation of the process on a non-partioned tab:

SQL> alter table x drop primary key
/

Table altered.


SQL> CREATE UNIQUE y ON x
(
aa ASC,
bb  ASC,
cc  ASC
 )
PCTFREE     2
INITRANS    2
MAXTRANS    255
TABLESPACE  b
STORAGE   (
INITIAL     64M
NEXT        64M
PCTINCREASE 0
MINEXTENTS  1
MAXEXTENTS  2147483645
)
 NOLOGGING
PARALLEL 10
/
CREATE UNIQUE INDEX y ON x
                    *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> 
SQL> ALTER TABLE x
ADD(CONSTRAINT "y"
PRIMARY KEY(aa,bb, cc))
/

Table altered.

Note : have changed the actual names of index(y) and table(x).

Why did it fail ? We never received this error in the past 10 months.

Thanks in advance,
Raj.

Tom Kyte
February 11, 2008 - 12:05 pm UTC

you are editing stuff - that is obvious.

I cannot tell from what you provided what you did wrong.

I can say that by using "y", you just made a lower case named thing - that would not conflict with UPPERCASE Y

sounds like someone added Y to your database and you cannot reuse that name.

A reader, February 11, 2008 - 12:52 pm UTC

give a

select * from user_objects where upper(object_name)='Y'

to see what is that object

Primary key index

Raj, February 13, 2008 - 3:44 am UTC

Have posted the rebuild log of an index.

04:40:36 SQL> alter table duns_product_revenue_summary drop primary key
04:40:36   2  /

Table altered.

04:40:36 SQL> 
04:40:36 SQL> CREATE UNIQUE INDEX pk_dun_product_rev_summary ON duns_product_revenue_summary
04:40:36   2    (
04:40:36   3      duns_number                     ASC,
04:40:36   4      product_code                    ASC,
04:40:36   5      time_period_date                ASC,
04:40:36   6      state_code                      ASC
04:40:36   7    )
04:40:36   8    PCTFREE     2
04:40:36   9    INITRANS    2
04:40:36  10    MAXTRANS    255
04:40:36  11    TABLESPACE  tg_dprs_idx
04:40:36  12    STORAGE   (
04:40:36  13      INITIAL     256M
04:40:36  14      NEXT        64M
04:40:36  15      PCTINCREASE 0
04:40:36  16      MINEXTENTS  1
04:40:36  17      MAXEXTENTS  500
04:40:36  18    )
04:40:36  19  NOLOGGING
04:40:36  20  PARALLEL 4
04:40:36  21  /
CREATE UNIQUE INDEX pk_dun_product_rev_summary ON duns_product_revenue_summary
                    *
ERROR at line 1:
ORA-00955: name is already used by an existing object


04:40:37 SQL> 
04:40:37 SQL> ALTER TABLE duns_product_revenue_summary
04:40:37   2      ADD(CONSTRAINT "pk_dun_product_rev_summary"
04:40:37   3  PRIMARY KEY(DUNS_NUMBER, PRODUCT_CODE, TIME_PERIOD_DATE, STATE_CODE))
04:40:37   4  /
PRIMARY KEY(DUNS_NUMBER, PRODUCT_CODE, TIME_PERIOD_DATE, STATE_CODE))
*
ERROR at line 3:
ORA-14063: Unusable index exists on unique/primary constraint key


04:40:37 SQL> ALTER INDEX pk_dun_product_rev_summary LOGGING NOPARALLEL;

Index altered.

-------------------------
SQL> select * from user_objects where object_name=upper('pk_dun_product_rev_summary');

OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
PK_DUN_PRODUCT_REV_SUMMARY
                                  2610869        2610869 INDEX
26-jan-08 30-jan-08 2008-01-26:11:42:20 VALID   N N N

SQL> select * from user_objects where object_name=lower('pk_dun_product_rev_summary');

no rows selected


-----------------
Let me know if any other info is required.

Tom Kyte
February 13, 2008 - 1:05 pm UTC

it clearly shows you already have the index, I'm not sure where your confusion lies here.

Primary Key

Raj, February 13, 2008 - 3:50 am UTC

Sorry, i missed the re-creation part.
I dropped the index in-order the script to work.

SQL > drop index pk_dun_product_rev_summary;

Index Dropped.

11:42:20 SQL> alter table duns_product_revenue_summary drop primary key
11:42:20   2  /
alter table duns_product_revenue_summary drop primary key
*
ERROR at line 1:
ORA-02441: Cannot drop nonexistent primary key


11:42:20 SQL> 
11:42:20 SQL> CREATE UNIQUE INDEX pk_dun_product_rev_summary ON duns_product_revenue_summary
11:42:20   2    (
11:42:20   3      duns_number                     ASC,
11:42:20   4      product_code                    ASC,
11:42:20   5      time_period_date                ASC,
11:42:20   6      state_code                      ASC
11:42:20   7    )
11:42:20   8    PCTFREE     2
11:42:20   9    INITRANS    2
11:42:20  10    MAXTRANS    255
11:42:20  11    TABLESPACE  tg_dprs_idx
11:42:20  12    STORAGE   (
11:42:20  13      INITIAL     256M
11:42:20  14      NEXT        64M
11:42:20  15      PCTINCREASE 0
11:42:20  16      MINEXTENTS  1
11:42:20  17      MAXEXTENTS  500
11:42:20  18    )
11:42:20  19  NOLOGGING
11:42:20  20  PARALLEL 4
11:42:20  21  /

Index created.

13:01:59 SQL> 
13:01:59 SQL> ALTER TABLE duns_product_revenue_summary
13:01:59   2      ADD(CONSTRAINT "pk_dun_product_rev_summary"
13:01:59   3  PRIMARY KEY(DUNS_NUMBER, PRODUCT_CODE, TIME_PERIOD_DATE, STATE_CODE))
13:01:59   4  /

Table altered.

13:01:59 SQL> ALTER INDEX pk_dun_product_rev_summary LOGGING NOPARALLEL;

Index altered.

Thanks,
Raj.

DROP PRIMARY KEY doesn't drop the UNIQUE INDEX

A reader, September 18, 2008 - 5:16 pm UTC

Version is 9208.

If I DROP PRIMARY KEY it doesn't drop the UNIQUE INDEX.

Is this a BUG ?
It happens intermittantly. Sometimes it drops sometimes it doesnot.
Tom Kyte
September 18, 2008 - 9:59 pm UTC

I don't think it is "intermittent" - it should either happen or not

but in any case, very quick solution

alter table t drop constraint t_pk drop index;


DROP PRIMARY KEY doesn't drop the UNIQUE INDEX

A reader, September 18, 2008 - 11:58 pm UTC

In 9208 is it supposed to happen Or
It should not Happen ?

( Where :It means Index is dropped )

If Index is not Dropped Can we call it a BUG.

I agree behaviour or BUG should be consistent.

Tom Kyte
September 19, 2008 - 7:38 am UTC

It won't drop the index if the index was separately created and non-unique.

When I said it would be consistent - I meant "if you do the same set of steps - it would either drop it or not - consistently"

So, I think you are doing something here - not sure what - and getting confused somewhere along the line. The index that is left behind is probably non-unique.

Unless you can show us an example...


In 9i, the drop constraint would drop the unique index. Going forward - if the index is created separately, it will not drop the index. You would use drop index on the drop constraint clause.


ops$tkyte%ORA9IR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA9IR2> create index t_idx on t(x);

Index created.

ops$tkyte%ORA9IR2> alter table t add constraint t_pk primary key(x);

Table altered.

ops$tkyte%ORA9IR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_IDX                          NONUNIQUE

ops$tkyte%ORA9IR2> alter table t drop constraint t_pk ;

Table altered.

ops$tkyte%ORA9IR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_IDX                          NONUNIQUE

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> drop table t;

Table dropped.

ops$tkyte%ORA9IR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA9IR2> create UNIQUE index t_idx on t(x);

Index created.

ops$tkyte%ORA9IR2> alter table t add constraint t_pk primary key(x);

Table altered.

ops$tkyte%ORA9IR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_IDX                          UNIQUE

ops$tkyte%ORA9IR2> alter table t drop constraint t_pk ;

Table altered.

ops$tkyte%ORA9IR2> select index_name, uniqueness from user_indexes where table_name = 'T';

no rows selected



and in 10g and above:

ops$tkyte%ORA10GR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> create index t_idx on t(x);

Index created.

ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x);

Table altered.

ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_IDX                          NONUNIQUE

ops$tkyte%ORA10GR2> alter table t drop constraint t_pk ;

Table altered.

ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_IDX                          NONUNIQUE

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> create UNIQUE index t_idx on t(x);

Index created.

ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x);

Table altered.

ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_IDX                          UNIQUE

ops$tkyte%ORA10GR2> alter table t drop constraint t_pk ;

Table altered.

ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_IDX                          UNIQUE

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>

<b>Note however that an implicitly created index will be dropped still</b>


ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x);

Table altered.

ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_PK                           UNIQUE

ops$tkyte%ORA10GR2> alter table t drop constraint t_pk ;

Table altered.

ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';

no rows selected

Your above example

Nitin, February 06, 2009 - 12:39 am UTC

Hi Tom,
the example above you told looks some what different to me when i did on my DB.

In your example
=======
ops$tkyte%ORA9IR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA9IR2> create index t_idx on t(x);

Index created.

ops$tkyte%ORA9IR2> alter table t add constraint t_pk <b>primary key</b>(x);

Table altered.

ops$tkyte%ORA9IR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_IDX                          <b>NONUNIQUE</b>

ops$tkyte%ORA9IR2> alter table t drop constraint t_pk ;

Table altered.

ops$tkyte%ORA9IR2> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_IDX                          <b>NONUNIQUE</b>


=================
On my DB it shows(OS WINDOWS XP PROF-SP2)
===============

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE    8.1.7.0.0       Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

<code>SQL> create table t ( x int, y int );

Table created.

SQL> alter table t add constraint t_pk primary key(x);

Table altered.

SQL> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_PK                           <b>UNIQUE</b>

SQL> alter table t drop constraint t_pk ;

Table altered.

SQL> select index_name, uniqueness from user_indexes where table_name = 'T';

no rows selected


SQL> discon
Disconnected
SQL> conn apps/*****@****
Connected.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL>  create table t ( x int, y int );

Table created.

SQL> alter table t add constraint t_pk primary key(x);

Table altered.

SQL> select index_name, uniqueness from user_indexes where table_name = 'T';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_PK                           <b>UNIQUE</b>

SQL> alter table t drop constraint t_pk ;

Table altered.

SQL> select index_name, uniqueness from user_indexes where table_name = 'T';

no rows selected


SQL>
</code>

Is it related to Version difference?

Thank you in advance!

Tom Kyte
February 06, 2009 - 3:29 pm UTC

you didn't create the index, your example differs from mine.

mine:

ops$tkyte%ORA9IR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA9IR2> create index t_idx on t(x);

Index created.

ops$tkyte%ORA9IR2> alter table t add constraint t_pk <b>primary key</b>(x);

Table altered.


yours:

<code>SQL> create table t ( x int, y int );

Table created.

SQL> alter table t add constraint t_pk primary key(x);

Table altered.



so it is related to you running different code.