Primary Keys with local indexes

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: 2000/08/09
Message-ID: <3991D39F.890C3D73@0800-einwahl.de>#1/1

Hi all,

we want to enforce a primary key constraint in 8.1.6.1 in a partitioned table where the partition key is not part of the index columns. The doc reads

"Note that:

Constraints use existing indexes; they do not create indexes unless necessary.

Unique and primary keys can use non-unique as well as unique indexes. They can even use just the first few columns of non-unique indexes.

At most one unique or primary key can use each non-unique index.

The column orders in the index and the constraint do not need to match.

If you need to check whether an index is used by a constraint, for example when you want to drop the index, the object number of the index used by a unique or primary key constraint is stored in CDEF$.ENABLED for that constraint. It is not shown in any catalog view. "

This works very well for non-partitioned tables:

SQL>
SQL> drop table test cascade constraints;

Table dropped.

SQL>
SQL> create table test (

  2  	     n integer
  3  	     , d date

  4 )
  5 /*
  6 partition by range (d)
  7 (
  8 partition test_p1 values less than (to_date ('01.01.2000',
'DD.MM.YYYY'))

  9 , partition test_p2 values less than (to_date ('01.01.2001',
'DD.MM.YYYY'))

 10 , partition test_p3 values less than (maxvalue)  11 )
 12 */
 13 /

Table created.

SQL>
SQL> create index test_i
  2 on test (n)
  3 /*
  4 global
  5 partition by range (d)
  6 (
  7 partition test_p1 values less than (to_date ('01.01.2000',
'DD.MM.YYYY'))

  8 , partition test_p2 values less than (to_date ('01.01.2001',
'DD.MM.YYYY'))

  9 , partition test_p3 values less than (maxvalue)  10 )
 11 */
 12 /

Index created.

SQL>
SQL> alter table test add (constraint test_pk unique (n));

Table altered.

SQL>
SQL> select index_name, partitioned, uniqueness from user_indexes where table_name = 'TEST'
  2 /

TEST_I                         NO  NONUNIQUE                                    

SQL>
SQL> spool off

However, this does not work with partitioned tables:

SQL>
SQL> drop table test cascade constraints;

Table dropped.

SQL>
SQL> create table test (

  2  	     n integer
  3  	     , d date

  4 )
  5 partition by range (d)
  6 (
  7 partition test_p1 values less than (to_date ('01.01.2000',
'DD.MM.YYYY'))

  8 , partition test_p2 values less than (to_date ('01.01.2001',
'DD.MM.YYYY'))

  9 , partition test_p3 values less than (maxvalue)  10 )
 11 /

Table created.

SQL>
SQL> create index test_i
  2 on test (n)
  3 local
  4 /

Index created.

SQL>
SQL> alter table test add (constraint test_pk unique (n)); alter table test add (constraint test_pk unique (n)) *
ERROR at line 1:
ORA-01408: such column list already indexed

SQL>
SQL> select index_name, partitioned, uniqueness from user_indexes where table_name = 'TEST'
  2 /

TEST_I                         YES NONUNIQUE                                    

SQL>
SQL> spool off

What can I do? Please help.

Martin Received on Wed Aug 09 2000 - 00:00:00 CDT