Skip to content
Advertisement

HANA: How to add a unique contraint on a column that is already indexed?

Having the following schema:

CREATE TABLE test_table(
    cryptid varchar(255) 
);
CREATE INDEX cryptid_index ON test_table (cryptid);

I am trying to a unique contraint to the column.

ALTER TABLE test_table ADD constraint crypid_unique_contraint UNIQUE(cryptid);

But this runs into an error:

Could not execute 'ALTER TABLE test_table ADD constraint crypid_unique_contraint ...'
Error: (dberror) [261]: invalid index name: column list already indexed

I can understand that the column is already indexed because I have created the index by myself. But I want the column to be unique. Is there a way to do this?

Advertisement

Answer

This is indeed an undocumented limitation in the current HANA versions.

The only way to create a unique constraint on this column is to first drop the single-column index present on this column.

I would consider the fact that this is not documented a (docu-)bug. However, the fact that existing indexes cannot be generally reused for uniqueness checks is not. Single-column indexes in HANA’s column store (which is what you use by default) tables are not B-tree indexes. Instead, these are inverted indexes into the column store structure of the main store-part of a column store table column.
These inverted structures cannot be checked for duplicates in the current transactional context as easily as B-tree indexes could.

This, I believe, is the reason for
a) implementing the uniqueness check only on a specific index implementation in the column store,
and
b) making the system behavior (not allowing the “conversion” of an existing index into a unique index) consistent across all table types.

As a general comment: for column store tables the benefit of single-column indexes for lookup/point-read scenarios is very often not worth the additional storage & compute resource consumption. This type of index practically doubles the memory requirement for the indexed column. So the speed-up in looking up a specific value should be worthwhile this additional permanent resource consumption.

9 People found this is helpful
Advertisement