Skip to end of metadata
Go to start of metadata

 

Version


Altibase version 6.1.1 or earlier

 

Symptom


The following error occurs when creating PK or UNIQUE INDEX in the partition table.

[ERR-31283: Unable to create a primary key or a unique key constraint in the local non-prefixed index.]

 

Cause


The description of the error can be checked using the altierr utility as follows.

$ altierr 0x31283

0x31283 ( 201347) qpERR_ABORT_QDX_NOT_ALLOWED_PRIMARY_AND_UNIQUE_KEY_OF_NONE_PREFIXED_INDEX Unable to create a primary key or a unique key constraint in the local non-prefixed index.
# - The user tried to create a primary key or a unique key constraint in the local prefixed index.
# *Action:
# - Please do not create a primary key or a unique key constraint in the local non-prefixed index.

The global index is not supported in versions of Altibase 6.1.1 or earlier.

Therefore, all partition indexes are local indexes, and local non-prefixed indexes cannot be created with PK or UNIQUE INDEX.

The reason that local non-prefixed indexes cannot be created with PK or UNIQUE INDEX is that even if the column value is the only value within a specific partition, it cannot be guaranteed to be unique across the table.

(To check the UNIQUE attribute in the entire table, the entire partition must be examined, but the local index checks the UNIQUE attribute only within a specific partition.)

Solution


1. PK or UNIQUE INDEX must be created as a prefixed index. That is, PK or UNIQUE INDEX must have the same partitioning key column and index column.

2. In order to create an index with a column that is not the same as the partitioning key column, it can be created with NON-UNIQUE INDEX.

3. If it is upgraded to Altibase version 6.3.1 or higher, PK or UNIQUE INDEX can be created as a global index.

 

# Example

1. An example of creating a local prefixed index as PK by changing the PK column order.

2. An example of creating a local non-prefixed index as a non-unique index without creating a PK.

3. An example of creating a PK with a global index after upgrading to version 6.3.1.

 

Reference


# Index type for the partitioned table

Class condition 1

Class condition 2

Class condition 3

Index type

Support/Not Supported

The index is partitioned.

index part key = table part key

index part key = index key

(Partitioned) Local prefixed Index

Supported

 

 

index part key != index key

(Partitioned) Local nonprefixed Index

Supported

 

index part key != table part key

index part key = index key

(Partitioned) Global prefixed Index

Not supported

 

 

index part key != index key

(Partitioned) Global nonprefixed Index

Not supported

The index is not partitioned.

 

 

Nonpartitioned global index

Supported in version 6.3.1 or later

The distinction between prefixed and nonprefixed indexes is due to the unique property.

In the case of a nonprefixed index, even if it is unique within a partition, it is not guaranteed to be unique across the table.

Therefore, nonprefixed indexes cannot be created with PK or UNIQUE INDEX.

  • No labels