SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




Install SAP Free


Create Check Constraint with Between on HANA Database Table Numeric Column

SQLScript developers who create database tables can create check constraints for numeric fields to be entered only a range of values that can be compared using BETWEEN operator for data validity. For example SQL programmer on SAP HANA database may want to limit the values of a table column of YEAR to be a value between 1900 and 2000, etc. Then a check constraint can be defined to keep table data consistent.

Check Constraint using Between for HANA 2.0 Database

Assume that SQL developer created below sample database table on HANA database

CREATE COLUMN TABLE SalesOrderHeader (
 SalesOrderID int,
 "Year" int
)

Now it is possible to add a new check constraint on database table for "Year" field so that every entry should be between 1900 and 2000. Any other value our of this range on Year field will be automatically refected by the database model.

ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Year CHECK ( "Year" BETWEEN 1900 AND 2000 );

Check constraint is successfully created on SAP HANA database table when an INSERT command is executed for a database row having a Year field value out of the range 1900 and 2000 below SQL exception occurs.

Insert Into SalesOrderHeader Select 1, 1462 From Dummy;

Could not execute 'Insert Into SalesOrderHeader Select 1, 1462 From Dummy' in 142 ms 169 µs .
SAP DBTech JDBC: [677]: check constraint violation: CK_SALESORDERHEADER_YEAR: "Year" BETWEEN 1900 AND 2000

Since the CHECK constraint prevents SQL developers to add new rows and execute INSERT DML statements on the HANA database table for unwanted Year values, this table check constraint is a good control method on database layer for data validity.

It is possible to remove the check constraint using following DROP CONSTRAINT statement

ALTER TABLE SalesOrderHeader DROP CONSTRAINT CK_SalesOrderHeader_Year;

I tried to create the same CHECK constraint as follows using AND condition and managed to add same control successfully.

ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Year CHECK ( "Year" >= 0 AND "Year" <= 8 );

My last trick is creating two separate CHECK constraints as follows on HANA table.

ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Year_Min CHECK ( "Year" >= 1900 );
ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Year_Max CHECK ( "Year" <= 2000 );

All these check constraints enable HANA database SQL developers to control data inserted into database table and to store only the valid column values.


Check Constraint using Between for HANA 1.0 Database

Unfortunately, if you are working on a HANA database with version 1.0, although you can create the same check constraint with BETWEEN expression as follows

ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Year CHECK ( "Year" BETWEEN 1900 AND 2000 );

An INSERT command fails and SQL Engine throws below error message

SAP DBTech JDBC: [7]: feature not supported: [new expression] Only comparison condition for DML update is supported

As the second step to implement check constraint for Year field value validity, first I dropped the Check Constraint as I showed above, I executed following command

ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Year CHECK ( "Year" >= 0 AND "Year" <= 8 );

This check constraint creation code fails on SAP HANA database version 1.0 though it is successfully created on SAP HANA 2.0

Could not execute 'ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Year CHECK ( "Year" >= 0 AND "Year" <= ...' in 159 ms 328 µs .
[301]: unique constraint violated: Table(P_INDEXCOLUMNS_), Index(_SYS_TREE_RS_#131886_#0_#P0)

With a hope I tried the third option which is creating two check constraints for the target table field instead of a single constraint with Between operators.

ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Year_Min CHECK ( "Year" >= 0 );
ALTER TABLE SalesOrderHeader ADD CONSTRAINT CK_SalesOrderHeader_Year_Max CHECK ( "Year" <= 8 );

And luckily this last option runs successfully on SAP HANA 1.0
As HANA SQLScript programmer if you are developing applications on HANA database version 1.0 it is possible to split the check constraint into two instead of single constraint using BETWEEN expression.






SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table


Meetup Sunumu 1 meetup






Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems