Feature not Supported: Unique Index on Generated Column
SAP HANA database does not support the feature to create unique index on generated columns of a database table. HANA SQLScript developers can create unique index on database table columns unless the table column is a generated column (aka calculated column).
For example, if the SQL database programmer tries to create a column table with a unique constraint on a generated column as follows;
CREATE COLUMN TABLE CodeTable (
ID int generated by default as IDENTITY(start with 1 increment by 1) NOT NULL,
Code nvarchar(25) as (concat('Code-', TO_VARCHAR(ID))),
Name nvarchar(50),
CONSTRAINT UI_CodeTable_Code UNIQUE (Code)
);
You see Code column is a generated or calculated column. It produces values like Code-1, Code-2, Code-3, etc. Id is automatically created as an identity value and a constant string "Code-" is concatenated with this ID value.
The execution of the above SQLScript Create Table command fails with below error message.
The SQL exception indicates that creation of unique index on generated column is not supported on HANA database.
Could not execute 'CREATE COLUMN TABLE CodeTable ( ID int generated by default as IDENTITY(start with 1 increment by ...'
SAP DBTech JDBC: [7]: feature not supported: unique index on generated column: CODE: line 3 col 9 (at pos 129)
If you omit the unique index constraint from the DDS statement CREATE TABLE, the HANA database table will be created successfully. Unfortunately, there is not a way of creating unique index on calculated fieds or generated columns. But a work-around might be creating unique indices on the input argument fields, if it makes sense.
