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


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);    


/wp-content/uploads/2015/01/1_632897.png


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


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

/wp-content/uploads/2015/01/2_632898.png

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.


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);

/wp-content/uploads/2015/01/3_632899.png


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);

/wp-content/uploads/2015/01/4_632909.png

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

Altering the identity column:

We cannot alter the identity column after it has been defined.

ALTER TABLE T_PRODUCT_DETAILS1 ALTER (PID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 2000 INCREMENT BY 3));

       

Could not execute ‘ALTER TABLE T_PRODUCT_DETAILS1 ALTER (PID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 2000 …’ in 267 ms 367 µs .

SAP DBTech JDBC: [7] (at 38): feature not supported: cannot modify identity column: PID: line 1 col 39 (at pos 38)

                       

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Mert Karakilic

    Thanks for the post. Keep in mind, identity feature is a fancy way of creating a sequence. Under the hood, it creates a sequence by the internal ID of the column hence the funny sequence names under the schema/sequences. Just like manual creation of the sequences, if you don’t want trouble while inserting by key, you need to either create the sequence yourself rather than the IDENTITY version. Then you can reset the sequence by writing a simple procedure to drop and recreate it. They need to improve this identity logic for sure. Far too complicated for a simple auto-increment functionality.

    (0) 

Leave a Reply