SAP HANA Database SQLScript Identity Column and Current_Identity_Value() Function

SAP HANA SQLScript developers can create database tables with identity column. SQL programmers can use SQLScript Current_Identity_Value() function to fetch most recent inserted identity column value. In this SQLScript tutorial, I want to share a SQL code block demonstrating how to create a database table with identity column. Using Current_Identity_Value() function enables developers to read the identity column value right after a data record is inserted into the database table.

Let's now create two database tables Orders and OrderDetails.
In Orders table, we will create OrderId column as Identity column.
When an order is created, OrderId column value will be automatically populated.

First, let's see how identity table column is created using SQLScript code in a SAP HANA database.


create column table Orders (

 OrderId int generated by default as identity(start with 1 increment by 1) not null,

 SoldTo int not null,

 OrderDate date default current_date not null,

 Amount decimal(18,3),

 Currency varchar(3),

 SalesRepresentative smallint

);

Code

In "Create Table" DDL command, the identity column is created with "Generated by Default as Identity()" clause.
Identity clause also takes two arguments; first the starting value and the incremental value.
When the identity function is used for the first time, the "start with" value is used in Insert command.
In following Insert statements, the incremental value is added to the previous Identity value and used in the current command.

create table with identity column in SAP HANA database

Let's now create OrderDetails table with OrderId field, which will be the identity field value from Orders table.
OrderDetails table OrderId column value must exist in Orders table OrderId column.
So SQL developers must define the OrderId column in OrderDetails table as a foreign key referencing to header table.


create column table OrderDetails (

 OrderId int,

 Material varchar(50),

 Quantity smallint,

 foreign key (OrderId) references Orders(OrderId)

);

Code

When a foreign key constraint is defined on a HANA database table, records with values that do not exist in the referenced table field cannot be inserted to referencing table.

define foreign key in HANA database table using SQLScript

For example, if database developer tries to insert a row with foreign key column value which the referenced table does not contain, HANA database will throw the SQL exception shown below.


insert into OrderDetails values (100,'non-existing OrderId',1);

Code

10:52:38 PM (SQL Editor) Could not execute 'insert into OrderDetails values (100,'non-existing OrderId',1)'
Error: (dberror) 461 - foreign key constraint violation: TrexUpdate failed on table 'KODYAZ:ORDERDETAILS' with error: Row Locking failed on referenced Table;from table KODYAZ:ORDERDETAILS to table KODYAZ:ORDERS, expected rowlocks 1 but processed rowlocks 0 differ, rc=1535

Main focus point for us which is indicating the real cause is foreign key constraint violation

Let's now create a SQLScript code block which first inserts a header row to Orders table, reads the identity field value and use this value while Order items are inserted into OrderDetails SAP HANA database table.

I declare integer variable OrderId
Then I create first Order with INSERT INTO statement.
The identity field OrderId column value is read by using SQLScript current_identity_value() function into OrderId variable.

While creating Order items by inserting rows into OrderDetails HANA table, I use the OrderId variable for foreign key column value.

The last two SELECT statements are just for to see the results of the Insert statements.


DO BEGIN



declare OrderId integer;



insert into Orders (SoldTo,Amount,Currency,SalesRepresentative) values (10,80,'GBP',101);

select current_identity_value() into OrderId from dummy;



--select :OrderId as OrderNo from dummy;



insert into OrderDetails values (:OrderId, 'SAP HANA Database SQLScript',1);



select * from Orders where OrderId = :OrderId;

select * from OrderDetails where OrderId = :OrderId;



END;

Code

The output for the first Insert command execution will create the OrderId value as 1.
This is what we expect from the Orders table identity field definition.
The Identity column is created in HANA database table with "start with 1 increment by 1" clause.
Increment clause effect can be observed right after the second insert on Orders table.

SQLScript codes to get identity column value and force data integrity with foreign key

In short, above SQLScript code block simulated how data can be inserted into HANA database tables with parent-child relation.
Parent table created in HANA database with a DDL statement defining the identity column.
Additionally, the child table is linked to the parent table over the foreign key column value.
As we have tested, foreign keys are enabling data consistency on HANA database tables.