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.
