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.
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.