Primary Key based on non-unique local index

From: Vsevolod Afanassiev <vafanassiev_at_aapt.com.au>
Date: 23 Jan 2003 23:29:10 -0800
Message-ID: <4f7d504c.0301232329.795dacd8@posting.google.com>

First, a simple Test#1 using Oracle8i (8.1.6).

----------------------- beginning of Test#1 ------------------------

drop table test;
create table test
(

  n1 number,
  n2 number,
  n3 number
);

create index test_pk on test(n1);
alter table test add (constraint test_pk primary key (n1) using index);

---------------------- end of Test#1 ---------------------------------

Everything works fine. If I attempt to insert a duplicate value in column n1, it is rejected.

Now, the same test but the table is range-partitioned on column n2 and index is local:

--------------------- beginning of Test#2 -----------------------

drop table test;
create table test
(

  n1 number,
  n2 number,
  n3 number
)
partition by range (n2)
(

   partition p1 values less than (-10),
   partition p2 values less than (0),
   partition p3 values less than (10)

);

create index test_pk on test(n1) local;
alter table test add (constraint test_pk primary key(n1) using index);

-------------------------- end of Test#2 ------------------------

Note that index name is the same as constraint name, but this should be OK as indexes and constraints are in different Namespaces
(see Oracle SQL Reference, Chapter 2 "Basic Elements of Oracle SQL",
section "Schema Object Names and Qualifiers'). And it worked for Test#1.

However, when Test#2 is executed, ALTER TABLE fails with ORA-00995: name is already used by an existing object

Let's modify index name and make it different from constraint name: alter table test add (constraint testpk primary key(n1) using index);

In this case it fails with
ORA-01408: such column list already indexed

IMHO both error messages are wrong: I don't have a constraint with the same name and I don't have another index on the same column - the table has been dropped just before being created.

* * *

I think the following situation is quite common: You have a large historic table (for example, TRANSACTIONS). The table has Primary Key on Transaction_ID populated from a sequence. The table also has TRANSACTION_DATE column, and you want to partition the table by this column without mofifying Primary Key. Is it possible?

BTW, I got exactly the same result in 9.2.0

Thanks, Sev Received on Fri Jan 24 2003 - 01:29:10 CST