Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3
Table of Contents

Version

6.1.1 or below

Explanation

Unable to create a non-prefixed index on a partitioned table using the primary key or unique key.

This error message is output when a user tries to create a primary key or a unique key constraint in the local non-prefixed index.

Cause

The following error description can be viewed with the AltiErr utility: 

Panel

$ 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 for ALTIBASE HDB 6.1.1 or below.

Therefore, all partitioned indexes are local indexes and local non-prefixed indexes cannot be created for the primary key or unique index.

This is because even if a column value ​​within a particular partition is unique, its uniqueness within a table cannot be guaranteed.

(The entire partition must be scanned to check the unique property of a table but a local index only needs to check the unique property within a certain partition.)

Action

1. The prefixed index needs to be created with a primary key or unique index. In other words, the partition key column and the index column should be the same for a primary key or unique index.

2. You can create a non-unique index if you want to create an index with a column that is not the same as the partition key column.

3. It is possible to create a primary key or unique index with a global index if you upgrade to ALTIBASE HDB 6.3.1 or above.

# Examples

Code Block
languagesql
iSQL> CREATE TABLE REALSET_CONTENTS
2 (
3 CT_ID VARCHAR (32) NOT NULL,
4 CT_TYPE VARCHAR (2) NOT NULL,
5 CT_PATH VARCHAR (256) NOT NULL,
6 CT_URL VARCHAR (256) NOT NULL,
7 REG_DATE DATE NOT NULL,
8 FILE_NAME VARCHAR (256) NOT NULL,
9 STATUS VARCHAR (4) NOT NULL
10 )
11 PARTITION BY RANGE (REG_DATE)
12 (
13 PARTITION P_1 VALUES LESS THAN (to_date('2013-05-01', 'YYYY-MM-DD')),
14 PARTITION P_2 VALUES LESS THAN (to_date('2013-09-01', 'YYYY-MM-DD')),
15 PARTITION P_DEF VALUES DEFAULT
16 )
17 TABLESPACE SYS_TBS_DISK_DATA;
Create success.
iSQL> alter table REALSET_CONTENTS add primary key(CT_ID,REG_DATE);
Note

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

1. The following example changes the primary key column order and creates a local prefixed index for the primary key. 

Code Block
languagesql
iSQL> alter table REALSET_CONTENTS add primary key(REG_DATE,CT_ID);
Alter success.
iSQL> desc REALSET_CONTENTS
[ TABLESPACE : SYS_TBS_DISK_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME                                     TYPE                        IS NULL
------------------------------------------------------------------------------
CT_ID                                    VARCHAR(32)                 NOT NULL
CT_TYPE                                  VARCHAR(2)                  NOT NULL
CT_PATH                                  VARCHAR(256)                NOT NULL
CT_URL                                   VARCHAR(256)                NOT NULL
REG_DATE                                 DATE                        NOT NULL
FILE_NAME                                VARCHAR(256)                NOT NULL
STATUS                                   VARCHAR(4)                  NOT NULL
[ INDEX ]
------------------------------------------------------------------------------
NAME                                     TYPE     IS UNIQUE     COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_142                         BTREE    UNIQUE        REG_DATE ASC,
                                                                CT_ID ASC
[ PRIMARY KEY ]
------------------------------------------------------------------------------
REG_DATE, CT_ID

2. The following example creates a non-unique index (instead of a local non-prefixed index) for the primary key.

Code Block
languagesql
iSQL> create index REALSET_CONTENTS_IDX1 on REALSET_CONTENTS(CT_ID,REG_DATE) local;
Create success.
iSQL> desc REALSET_CONTENTS
[ TABLESPACE : SYS_TBS_DISK_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME                                     TYPE                        IS NULL
------------------------------------------------------------------------------
CT_ID                                    VARCHAR(32)                 NOT NULL
CT_TYPE                                  VARCHAR(2)                  NOT NULL
CT_PATH                                  VARCHAR(256)                NOT NULL
CT_URL                                   VARCHAR(256)                NOT NULL
REG_DATE                                 DATE                        NOT NULL
FILE_NAME                                VARCHAR(256)                NOT NULL
STATUS                                   VARCHAR(4)                  NOT NULL
[ INDEX ]
------------------------------------------------------------------------------
NAME                                     TYPE     IS UNIQUE     COLUMN
------------------------------------------------------------------------------
REALSET_CONTENTS_IDX1                    BTREE                  CT_ID ASC,
                                                                REG_DATE ASC
REALSET_CONTENTS has no primary key

3. The following example upgrades to version 6.3.1 and then creates a global index for the primary key. 

Code Block
languagesql
iSQL>  alter table REALSET_CONTENTS add primary key(CT_ID,REG_DATE);
Alter success.
iSQL> desc REALSET_CONTENTS
[ TABLESPACE : SYS_TBS_DISK_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME                                     TYPE                        IS NULL
------------------------------------------------------------------------------
CT_ID                                    VARCHAR(32)                 NOT NULL
CT_TYPE                                  VARCHAR(2)                  NOT NULL
CT_PATH                                  VARCHAR(256)                NOT NULL
CT_URL                                   VARCHAR(256)                NOT NULL
REG_DATE                                 DATE                        NOT NULL
FILE_NAME                                VARCHAR(256)                NOT NULL
STATUS                                   VARCHAR(4)                  NOT NULL
[ INDEX ]
------------------------------------------------------------------------------
NAME                                     TYPE     IS UNIQUE     COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_922                         BTREE    UNIQUE        CT_ID ASC,
                                                                REG_DATE ASC
[ PRIMARY KEY ]
------------------------------------------------------------------------------
CT_ID, REG_DATE

Reference

# Index types for partitioned tables

Classification 1

Classification 2

Classification 3

Index Type

Supported by Altibase

Partitioned Index

index part key = table part key

index part key = index key

(Partitioned) Local prefixed Index

Yes

 

 

index part key != index key 

(Partitioned) Local nonprefixed Index

Yes

 

index part key != table part key 

index part key = index key

(Partitioned) Global prefixed Index 

No

 

 

index part key != index key 

(Partitioned) Global nonprefixed Index

No

Non-partitioned Index

 

 

Non-partitioned global index

Supported for 6.3.1 and above

The difference between a prefixed index and a non-prefixed index is uniqueness.

A non-prefixed index cannot be created for the primary key or unique index because even if it is unique within the partition, it cannot be guaranteed to be unique within the entire table.