Create Table including Columns with Default Constraint on HANA Database
HANA database SQLScript developers can define default values for table columns created on SAP HANA database. Whenever a value is not specified explicitely for those table columns in SQL INSERT command, the default value provided during DDS (Data Definition Statement or DDL in general) command.
Let's assume as HANA database SQL programmer you create a table with an identity column, a timestamp column for keeping insert datetime value, a unique idendifier column and a text field with some dummy character value as default.
Here is the DDS (Data Definition Statement) CREATE TABLE command, SQL developers can execute to create table with specified columns.
create column table TableColumnDefaults (
Id int generated by default as identity(start with 1 increment by 1) not null,
Status varchar(10) not null default 'Initial',
InsertDT datetime not null default current_date,
-- InsertDT timestamp not null default current_date,
GUID varbinary(16) NOT NULL DEFAULT SYSUUID,
Name varchar(10)
);
Now SQLScript developers can populate some test data to see how default value for each column is assigned. Please note that below INSERT command provides only "name" column value all others are left for default constraints.
insert into TableColumnDefaults (Name) values ('SQLScript');
select * from TableColumnDefaults;
Resultant data enriched with table column default values assigned for NULL entries, is as follows.
Though as SQL programmer executing the INSERT command, we did not explicitely define a value for each of Id, Status, InsertDT and GUID fields, these fields are populated with values defined according to the criteria in CREATE TABLE command automatically.
For date and time fields, HANA database developers can either use "datetime" or "timestamp" data types.
create column table TableColumnDefaults (
Id int,
Status varchar(10),
InsertDT datetime,
GUID varbinary(16),
Name varchar(20)
Nationality varchar(20)
);
By executing below ALTER TABLE .. ALTER column commands, a SQLScript programmer can define default values for existing table columns easily.
Of course since below DDS statements force the altered database table columns to be not-null values, they work successfully if the table does not include row values that are NULL for mentioned columns.
ALTER TABLE TableColumnDefaults ALTER (Status varchar(10) NOT NULL DEFAULT 'Initial');
ALTER TABLE TableColumnDefaults ALTER (InsertDT datetime NOT NULL DEFAULT CURRENT_DATE);
ALTER TABLE TableColumnDefaults ALTER (GUID varbinary(16) NOT NULL DEFAULT SYSUUID);
If there are NULL values in the table at the execution time, following ALTER TABLE commands can be used.
ALTER TABLE TableColumnDefaults ALTER (Status varchar(10) DEFAULT 'Initial');
ALTER TABLE TableColumnDefaults ALTER (InsertDT datetime DEFAULT CURRENT_DATE);
ALTER TABLE TableColumnDefaults ALTER (GUID varbinary(16) DEFAULT SYSUUID);
I hope I could show how default constraints can be created in this SQLScript database programming tutorial for SAP HANA developers. Default constraints are useful if an explicit value is required but if you can handle NULL values these NULL column values will help you save database resources
