EXPERIENCE WITH IDENTITY FEATURE - SAP HANA

[former_member437726]

Introduction:

• SAP HANA has provided a feature to generate a column with auto incremented values from SPS8 onwards.


• I had the situation where I needed to generate a column automatically (generating ID for the products).


• When I searched for such an option in SAP HANA, I came across this feature.

http://scn.sap.com/community/hana-in-memory/blog/2014/06/04/quick-note-on-identity-column-in-sap-han....

 

Syntax:

 

CREATE COLUMN TABLE <table_name> (<column_name> <num_data_type> GENERATED BY DEFAULT AS IDENTITY [<SEQUENCE PARAMETERS>]);

CREATE COLUMN TABLE <table_name> (<column_name> <num_data_type> GENERATED ALWAYS AS IDENTITY [<SEQUENCE PARAMETERS>]);

<SEQUENCE PARAMETERS> :

START WITH <start_value>

                         | INCREMENT BY <increment_value>

                         | MAXVALUE <max_value>

                         | NO MAXVALUE

                         | MINVALUE <min_value>

                         | NO MINVALUE

                         | CYCLE

                         | NO CYCLE

                         | CACHE <cache_size>

                         | NO CACHE

 

Limitations:

 

• IDENTITY column generation is not possible with ROW store tables.


• Importing data from CSV/Control file is possible only when the column is created with GENERATED BY DEFAULT.


• To import data for GENERATED ALWAYS column, the column should not be a primary key and should accept NULL values.

 

 

Examining the IDENTITY feature:

 

Let’s look at the scenarios for IDENTITY column during table creation:

 

Scenario 1: DEFAULT option without specifying any range

 

CREATE COLUMN TABLE "ABC"."T_PRODUCT_DETAILS1"

("PID" BIGINT CS_FIXED GENERATED BY DEFAULT AS IDENTITY,

                                  "NAME" VARCHAR(30),

                                  "DESCRIPTION" VARCHAR(40),

                                  "PRICE" INTEGER CS_INT,

                                  PRIMARY KEY ("PID"));

 

• This will create the column table T_PRODUCT_DETAILS1 with an identity column “PID” which by default will start from 1.


• While inserting/updating values we CAN use the column “PID” as the column is generated BY DEFAULT.


• If a value is specified for PID, the value overrides the DEFAULT sequence number.


• If value is not specified during insert/update, then the sequence number is generated.

 

Example:

 

INSERT INTO T_PRODUCT_DETAILS1 (PID,NAME,DESCRIPTION,PRICE) VALUES (100,'MOUSE','INPUT DEVICE',870);    

Now, without specifying PID column, lets insert another record.

 

  INSERT INTO T_PRODUCT_DETAILS1 (NAME,DESCRIPTION,PRICE) VALUES ('Keyboard','INPUT DEVICE',700);    



We shall note that, now the default sequence number is getting generated for the record as we didn’t specify the column PID during insertion.

 

Issue faced:

 

Whenever we specify value during inserting or updating PID, we must ensure that it doesn’t overlap the sequence number. THOUGH this situation is unlikely because once the implementation is with IDENTITY, the insertion of the column value is auto generated and not MANUALLY inserted.

 

INSERT INTO T_PRODUCT_DETAILS1 (PID,NAME,DESCRIPTION,PRICE) VALUES(1,'MONITOR','OUTPUTDEVICE',14500);

 

Could not execute 'INSERT INTO T_PRODUCT_DETAILS1 (PID,NAME,DESCRIPTION,PRICE) VALUES(1,'MONITOR','OUTPUTDEVICE',14500)' in 270 ms 552 µs .

[301]: unique constraint violated: Table(T_PRODUCT_DETAILS1)

 

Vice-versa situation:

 

  INSERT INTO T_PRODUCT_DETAILS1 (PID,NAME,DESCRIPTION,PRICE) VALUES(2,'MONITOR','OUTPUTDEVICE',14500);

INSERT INTO T_PRODUCT_DETAILS1 (NAME,DESCRIPTION,PRICE) VALUES ('Voip','Communication',7300);                            

 

Could not execute 'INSERT INTO T_PRODUCT_DETAILS1 (NAME,DESCRIPTION,PRICE) VALUES ('Voip','Communication',7300)' in 272 ms 7 µs .

[301]: unique constraint violated: Table(T_PRODUCT_DETAILS1)

 

Here, the system tries to generate the next sequence number which is 2, but, we have already inserted a record with PID as 2.

       

Scenario 2: DEFAULT option with a range.

 

DROP TABLE T_PRODUCT_DETAILS1;

 

CREATE COLUMN TABLE "T_PRODUCT_DETAILS1"

("PID" BIGINT CS_FIXED GENERATED BY DEFAULT AS IDENTITY

            (START WITH 100 INCREMENT BY 1 maxvalue 120),

                                                "NAME" VARCHAR(30),

                                                "DESCRIPTION" VARCHAR(40),

                                               "PRICE" INTEGER CS_INT,

                                                PRIMARY KEY ("PID"));

 

This scenario is same as scenario 1 except that the sequence range is specified.

 

INSERT INTO T_PRODUCT_DETAILS1 (NAME,DESCRIPTION,PRICE) VALUES     

                                                                      ('Voip','Communication',7300);

Scenario 3: ALWAYS option with range

 

drop table T_PRODUCT_DETAILS1;

CREATE COLUMN TABLE "T_PRODUCT_DETAILS1" ("PID" BIGINT CS_FIXED GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 1 maxvalue 1200),

"NAME" VARCHAR(30),

"DESCRIPTION" VARCHAR(40),

"PRICE" INTEGER CS_INT,

PRIMARY KEY ("PID"));

 

 

This will create a column table T_PRODUCT_DETAILS1 but the identity column PID is auto-generated and NO OVERRIDE is allowed.

Example:

 

INSERT INTO T_PRODUCT_DETAILS1 (PID,NAME,DESCRIPTION,PRICE) VALUES(1,'MONITOR','OUTPUTDEVICE',14500);                            

 

Could not execute 'INSERT INTO T_PRODUCT_DETAILS1 (PID,NAME,DESCRIPTION,PRICE) VALUES(1,'MONITOR','OUTPUTDEVICE',14500)' in 266 ms 640 µs .

SAP DBTech JDBC: [406] (at 32): INSERT, UPDATE and UPSERT are disallowed on the generated field: cannot insert into generated identity column field PID: line 1 col 33 (at pos 32)

 

We cannot provide any value to the PID column as it is defined as “GENERATED ALWAYS”.