ALTER TABLE

Syntax

 ALTER TABLE <table_name> [<add_column_clause>] [<drop_column_clause>] [<alter_column_clause>] [<add_constraint_clause>]
                          [<drop_primary_key_clause>][<drop_constraint_clause>] [<preload_clause>] [<table_conversion_clause>]
                          [<move_clause>] [<add_range_partition_clause>] [<drop_range_partition_clause>] [<partition_clause>]
                          [<persistent_merge_option>] [<delta_log_option>] [<auto_merge_option>] [<unload_priority>]
                          [<schema_flexibility_option>]

Syntax elements:
<table_name>
<add_column_clause>
<drop_column_clause>
<alter_column_clause>
<add_constraint_clause>
<drop_primary_key_clause>
<drop_constraint_clause>
<preload_clause>
<table_conversion_clause>
<add_range_partition_clause>
<drop_range_partition_clause>
<partition_clause>
<persistent_merge_option>
<delta_log_option>
<auto_merge_option>
<unload_priority>
<schema_flexibility_option>

Syntax Elements

<table_name>

 <table_name>  ::= [<schema_name>.]<identifier>
 <schema_name> ::= <identifier>

The identifier of the table to be altered, with optional schema name.

<add_column_clause>

 <add_column_clause> ::= ADD ( {<column_definition> [<column_constraint>]} [{, <column_definition> [<column_constraint>]}...] )

Adds one or more columns to the specified table.

<drop_column_clause>

 <drop_column_clause> ::= DROP ( <column_name>[{, <column_name>} ...] )

Removes one or more columns from the specified table.

<alter_column_clause>

 <alter_column_clause> ::= ALTER ( <column_definition> [<column_constraint>] [{, <column_definition> [<column_constraint>]}...] )

Alters one or more column definitions.
Restrictions:

• To prevent data loss, only increasing the size of a column data type is allowed. For example, changing from nvarchar(20) to nvarchar(10) or from integer to tinyint will raise an error.
• ALTER does not currently follow data type conversion rules.
• Adding NOT NULL constraint to an existing column is allowed if either of the following cases are true:
  • The table is empty.
  • The default value is specified when the table contains data.

 <column_definition> ::= <column_name> <data_type> [<column_store_data_type>] [<ddic_data_type>] [DEFAULT <default_value>]
                         [GENERATED ALWAYS AS <expression>] [<schema_flexibility>] [<fuzzy_search_index>] [<fuzzy_search_mode>]

Defines a table column.

 <column_name> ::= <identifier>

The table column name.

 <data_type> ::= DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT | SMALLINT | INTEGER | BIGINT | SMALLDECIMAL | DECIMAL
               | REAL | DOUBLE | VARCHAR | NVARCHAR | ALPHANUM | SHORTTEXT | VARBINARY | BLOB | CLOB | NCLOB | TEXT

 <column_store_data_type> ::= CS_ALPHANUM | CS_INT | CS_FIXED | CS_FLOAT | CS_DOUBLE | CS_DECIMAL_FLOAT | CS_FIXED(p-s, s)
                            | CS_SDFLOAT | CS_STRING | CS_UNITEDECFLOAT | CS_DATE | CS_TIME | CS_FIXEDSTRING | CS_RAW
                            | CS_DAYDATE | CS_SECONDTIME | CS_LONGDATE | CS_SECONDDATE

 <ddic_data_type> ::= DDIC_ACCP | DDIC_ALNM | DDIC_CHAR | DDIC_CDAY | DDIC_CLNT | DDIC_CUKY | DDIC_CURR | DDIC_D16D
                    | DDIC_D34D | DDIC_D16R | DDIC_D34R | DDIC_D16S | DDIC_D34S | DDIC_DATS | DDIC_DAY  | DDIC_DEC
                    | DDIC_FLTP | DDIC_GUID | DDIC_INT1 | DDIC_INT2 | DDIC_INT4 | DDIC_INT8 | DDIC_LANG | DDIC_LCHR
                    | DDIC_MIN  | DDIC_MON  | DDIC_LRAW | DDIC_NUMC | DDIC_PREC | DDIC_QUAN | DDIC_RAW  | DDIC_RSTR
                    | DDIC_SEC  | DDIC_SRST | DDIC_SSTR | DDIC_STRG | DDIC_STXT | DDIC_TIMS | DDIC_UNIT | DDIC_UTCM
                    | DDIC_UTCL | DDIC_UTCS | DDIC_TEXT | DDIC_VARC | DDIC_WEEK

The available data types. Please see Data Types.

 DEFAULT <default_value> ::= NULL | <string_literal> | <signed_numeric_literal> | <unsigned_numeric_literal>

Specifies a value to be assigned to the column if an INSERT statement does not provide a value for the column

 GENERATED ALWAYS AS <expression>

Specifies the expression to generate the column value in runtime.

 <schema_flexibility> ::= [ENABLE | DISABLE] SCHEMA FLEXIBILITY

Specifies the column is dynamic:

• ENABLE produces a dynamic column.
• DISABLE (default value) produces a static column.

 <fuzzy_search_index> ::= FUZZY SEARCH INDEX [ON | OFF]

Turns a fuzzy search index on or off. OFF is the default.

 <fuzzy_search_mode> ::= FUZZY SEARCH MODE [<string_literal> | NULL]

Sets the fuzzy search mode with the value of <string_literal>. If NULL is specified, the fuzzy search mode is reset.

 <column_constraint> ::= NULL
                       | NOT NULL
                       | <unique_specification>
                       | <references_specification>

The column constraint rules.
For NULL, NOT NULL and <unique_specification>, see below.
For <references_specification>, see References Specification

 NULL

If NULL is specified it is not considered a constraint, it represents that a column that may contain a null value. The default is NULL.

 NOT NULL

The NOT NULL constraint prohibits a column value from being NULL.

 <unique_specification> ::= UNIQUE [<unique_tree_type_index>]
                          | PRIMARY KEY [<unique_tree_type_index>]

Specifies unique constraints. If the index type is omitted, the SAP HANA database chooses the appropriate index by considering the column data type. If the index type is not specified, the SAP HANA database will automatically select an index type as follows:

Index type	Criteria
CPBTREE	- character string types. - binary string types. - decimal types. - when the constraint is a composite key. - when the constraint is a non-unique constraint
BTREE	All other cases than specified for CPBTREE

 UNIQUE

Specifies a column as a unique key.
A composite unique key enables the specification of multiple columns as a unique key. With a unique constraint, multiple rows cannot have the same value in the same column.

 PRIMARY KEY

A primary key constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. It prohibits multiple rows from having the same value in the same column.

 <unique_tree_type_index> ::= BTREE | CPBTREE;

Specifies the index type.

 BTREE

BTREE specifies a B+-tree index. B+-tree is a tree that maintains sorted data which performs efficient insertion, deletion and search of records.

 CPBTREE

CPBTREE specifies a CPB+-tree index. CPB+-tree stands for Compressed Prefix B+-Tree, which is based on pkB-tree. CPB+-tree is a very small index because it uses 'partial key' that is only part of full key in index nodes. CPB+-tree shows better performance than B+-Tree for larger keys.

<add_constraint_clause>

 <add_constraint_clause> ::= ADD [CONSTRAINT <constraint_name>] <table_constraint>
 <constraint_name> ::= <identifier>

Adds a table constraint.

 <table_constraint> ::= <unique_constraint_definition> | <referential_constraint_definition>

The table constraint can be either a unique constraint or a referential constraint.

 <unique_constraint_definition> ::= <unique_specification> (<unique_column_name_list>)

The unique specification see Unique specifcation

 <unique_column_name_list> ::= <unique_column_name>[{, <unique_column_name>}...]

Speicfies the unique column name list which can have one or more column names.

 <unique_column_name> ::= <identifier>

A column name identifier.

 <referential_constraint_definition> ::= FOREIGN KEY (<referencing_column_name_list>) <references_specification>

Specifies a referential constraint.

 <referencing_column_name_list> ::= <referencing_column_name>[{, <referencing_column_name>}...]

Specifies the referencing column name list which can have one or more column names.

 <referencing_column_name> ::= <identifier>

The identifier of a referencing column.

 <references_specification> ::= REFERENCES <referenced_table> [(<referenced_column_name_list>)] [<referential_triggered_action>]

Specifies the referenced table, with optional column name list and trigger action. If <referenced_column_name_list> is specified, there will be one-to-one correspondence between <column_name> of <column_definition> (see column definition) and <referenced_column_name>. If it is not specified, there will be one-to-one correspondence between <column_name> of <column_definition> and the column name of the referenced table's primary key.

 <referenced_column_name_list> ::= <referenced_column_name>[{, <referenced_column_name>}...]

Specifies the referenced column name list, which can have one or more column names.

 <referenced_table> ::= <identifier>

The identifier of a table to be referenced.

 <referenced_column_name> ::= <identifier>

The identifier of the column name to be referenced.

 <referential_triggered_action> ::= <update_rule> [<delete_rule>]
                                  | <delete_rule> [<update_rule>]

Specifies an update rule with optional delete rule or a delete rule with optional update rule. The order in which they are provided provides an order of precedence for execution.

 <update_rule> ::= ON UPDATE <referential_action>
 <referential_action> ::= CASCADE | RESTRICT | SET DEFAULT | SET NULL

The following UPDATE referential actions are possible:

Action Name	Update Action
SET RESTRICT	Any updates to a referenced table are prohibited if there are any matched records in the referencing table. This is the default action.
CASCADE	If a record is updated in the referenced table, the corresponding records in the referencing table are also updated with the same values.
SET NULL	If a record is updated in the referenced table, the corresponding records in the referencing table are also updated with null values.
SET DEFAULT	If a record is updated in the referenced table, the corresponding records in the referencing table are also updated with their default values

 <delete_rule> ::= ON DELETE <referential_action>

The following DELETE referential actions are possible:

Action Name	Delete Action
SET RESTRICT	Any deletions to a referenced table are prohibited if there are any matched records in the referencing table. This is the default action.
CASCADE	If a record in the referenced table is deleted, the corresponding records in the referencing table are also deleted.
SET NULL	If a record in the referenced table is deleted, the corresponding records in the referencing table are set to null.
SET DEFAULT	If a record in the referenced table is deleted, the corresponding records in the referencing table are set to their default values.

<drop_primary_key_clause>

 <drop_primary_key_clause> ::= DROP PRIMARY KEY

Drops the primary key constraint.

<drop_constraint_clause>

 <drop_constraint_clause> ::= DROP CONSTRAINT <constraint_name>
 <constraint_name> ::= <identifier>

Drops a unique or referential constraint.

<preload_clause>

 <preload_clause> ::= PRELOAD ALL | PRELOAD ( <column_name> ) | PRELOAD NONE

Sets or removes the preload flag of the given tables or columns.
When the preload flag is set tables are automatically loaded into memory after an index server start. The current status of the preload flag is visible in the system table TABLES in the PRELOAD column. Possible values are 'FULL', 'PARTIALLY' and 'NO'. Also in system table TABLE_COLUMNS in column PRELOAD with possible values being 'TRUE' or 'FALSE'.

 PRELOAD ALL

Sets preload flags of all columns in the table.

 PRELOAD ( <column_name> )

Sets the flags of the specified column.

 PRELOAD NONE

Removes the preload flag from all columns.

<table_conversion_clause>

 <table_conversion_clause> ::= [ALTER TYPE] {ROW [THREADS <number_of_threads>] | COLUMN [THREADS <number_of_threads> [BATCH <batch_size>]]}

Converts the table storage from ROW to COLUMN or from COLUMN to ROW.

 ROW

Converts the table to ROW storage.

 COLUMN

Converts the table to COUMN storage.

 THREADS <number_of_threads>
 <number_of_threads> ::= <unsigned_integer>

Specifies how many parallel execution threads should be used for the table conversion. The optimal value for the number of threads is the number of available CPU cores. If THREADS is not provided the default value of the number of CPU cores specified in the indexserver.ini file will be used.

 BATCH <batch_size>
 <batch_size> ::= <unsigned_integer>

Specifies the number of rows to be inserted in a batch. If BATCH is not specified the default value of 2,000,000 will be used. Inserts into column tables will be immediately committed after every <batch_size> records have been inserted. BATCH option can be used only when a table is converted from ROW to COLUMN storage.

<move_clause>

 <move_clause> ::= MOVE [PARTITION <partition_number>] TO <indexserver_host_port> [PHYSICAL]
                 | MOVE [PARTITION <partition_number>] PHYSICAL

Moves a table to another location in a distributed environment.

 PARTITION <partition_number>
 <partition_number> ::= <unsigned_integer>

For partitioned tables, specifies the partition to be moved. If you attempt to move a partitioned table without specifying a <partition_number> an error is returned.

 <indexserver_host_port> ::= <unsigned_integer>

The internal indexserver port number where the table is to be moved.

 PHYSICAL

Specifies that a column store tables persistence storage is moved immediately to the target host. If the PHYSICAL option is not specified the table move will create a link inside the new host persistence pointing to the old host persistence. The link will be removed on the next merge or upon execution of another move operation not using the TO <indexserver_host_port> clause.

The PHYSICAL keyword is only for column store tables. Row store tables are always moved immediately.

<add_range_partition_clause>

 <add_range_partition_clause> ::= ADD <range_partition_clause>

Adds a partition for tables partitioned with RANGE, HASH RANGE, ROUNDROBIN RANGE.

 <range_partition_clause> ::= {<from_to_spec> | <single_spec>} [, PARTITION OTHERS]

The range specifier for a new partition.
For syntax details please see partition range specifier below.

<drop_range_partition_clause>

 <drop_range_partition_clause> ::= DROP <range_partition_clause>

Drops a partition for tables partitioned with RANGE, HASH RANGE, ROUNDROBIN RANGE.

<partition_clause>

 <partition_clause> ::= PARTITION BY <hash_partition> [, <range_partition> | , <hash_partition>]
                      | PARTITION BY <range_partition>
                      | PARTITION BY <roundrobin_partition> [,<range_partition>]

The PARTITION BY option partitions a table using the selected rules.
For more information about partitioning please see the "Table Partitioning in the SAP HANA database" section in the "SAP HANA Administration guide" available from the SAP HANA Appliance page.

 <hash_partition> ::= HASH (<partition_expression> [{<partition_expression>,}...]) PARTITIONS {<num_partitions> | GET_NUM_SERVERS()}

Partitions the created table using a hash partitioning scheme.

 <range_partition> ::= RANGE (<partition_expression>) (<range_spec>, ...)

Partitions the created table using a range partitioning scheme.

 <roundrobin_partition> ::= ROUNDROBIN PARTITIONS {<num_partitions> | GET_NUM_SERVERS()} [, <range_partition>]

Partitions the created table using a round robin partitioning scheme.

 GET_NUM_SERVERS()

Returns the number of servers available in the partitioning cluster.

 <range_spec> ::= {<from_to_spec> | <single_spec>} [{{<from_to_spec> | <single_spec>},} ...] [, PARTITION OTHERS]

The range specifier for a partition.

 <from_to_spec> ::= PARTITION <lower_value> <= VALUES < <upper_value>

Specifies a partition using lower and upper values of a <partition_expression>.

 <single_spec> ::= PARTITION VALUE = <target_value>

Specifies a partition using a single value of a <partition_expression>.

 PARTITION OTHERS

Specifies that all other values that are not covered by the partition specification will be gathered into one partition.

 <partition_expression> ::= <column_name> | YEAR(<column_name>) | MONTH(<column_name>)

The specifier used to segregate data into partitions.

 <lower_value> ::= <string_literal> | <numeric_literal>

The lower value of a partition specifier.

 <upper_value> ::= <string_literal> | <numeric_literal>

The upper value of a partition specifier.

 <target_value> ::= <string_literal> | <numeric_literal>

The target value of a single partition specifier.

 <num_partitions> ::= <unsigned_integer>

The number of partitions to be created for the table.

 <merge_partition_clause> ::= MERGE PARTITIONS 

Merges all parts of a partitioned table into a non-partitioned table.

<persistent_merge_option>

 <persistent_merge_option> ::= {ENABLE | DISABLE} PERSISTENT MERGE

Enables or disables persistent merging.

• When enabled the merge-manager uses persistent merges for the given table. This is the default behaviour.
• When disabled the merge-manager uses main-memory merges instead of persistent merges for the given table.

<delta_log_option>

 <delta_log_option> ::= {ENABLE | DISABLE} DELTA LOG

Enables or disables delta logging for table.

After enabling, you have to perform a savepoint to be certain that all data is persisted. Also you have to perform a data backup, otherwise it will not be possible to recover this data.

If logging is disabled, log entries will not be persisted for this table. Changes to this table will only be written to the data store when a savepoint is carried out. This can cause loss of committed transaction should the indexserver terminate. In the case of a termination, you have to truncate this table and insert all data again.

Note:

You should only use this option during initial load.

<auto_merge_option>

 <auto_merge_option> ::= {ENABLE | DISABLE} AUTOMERGE

Enables or disables automatic delta merge on the specified table.

<unload_priority>

 <unload_priority_option> ::= UNLOAD PRIORITY <unload_priority>

UNLOAD PRIORITY specifies that priority of table to be unloaded from memory.

 <unload_priority> ::= <digit>

Sets the priority of table to be unloaded from memory. It can be 0 ~ 9, where 0 means not-unloadable and 9 means earliest unload.

<schema_flexibility_option>

 <schema_flexibility_option> ::= {ENABLE | DISABLE} SCHEMA FLEXIBILITY

Enables or disables schema flexibility for the specified table.

Description

The ALTER TABLE statement changes the definition of a table.

Examples

You create Table t, and then alter default value of column b to 10.

 CREATE TABLE t (a INT, b INT);
 ALTER TABLE t ALTER (b INT DEFAULT 10);

You alter table t adding a new column c.

 ALTER TABLE t ADD (c NVARCHAR(10) DEFAULT 'NCHAR');

You create a primary key constraint, prim_key, on columns a and b of table t.

 ALTER TABLE t ADD CONSTRAINT prim_key PRIMARY KEY (a, b);

You change the table type of table t to COLUMN storage.

 ALTER TABLE t COLUMN;

You set the preload flags of column b and c on table t.

 ALTER TABLE t PRELOAD (b, c);

You partition table t with a RANGE partition, and then add an addtional partition.

 ALTER TABLE t PARTITION BY RANGE (a) (PARTITION VALUE = 1, PARTITION OTHERS);
 ALTER TABLE t ADD PARTITION 2 <= VALUES < 10;

You alter the table type of table t to a HISTORY table.

 ALTER TABLE t CREATE HISTORY;

You disable delta logging of table t.

 ALTER TABLE t DISABLE DELTA LOG;

You change the unload priority of table t to 2:

 ALTER TABLE t UNLOAD PRIORITY 2;

You create table R. Then you alter table R adding a unique constraint UK.

 CREATE TABLE R (A INT PRIMARY KEY, B NVARCHAR(10));
 ALTER TABLE R ADD CONSTRAINT UK UNIQUE (B);

You drop the unique constraint UK from table R.

 ALTER TABLE R DROP CONSTRAINT UK;

You create table S. You add a referential constraint FK to table S that references column A of table R with delete cascade option.

 CREATE TABLE S (FA INT, B NVARCHAR(10));
 ALTER TABLE S ADD CONSTRAINT FK FOREIGN KEY(FA) REFERENCES R(A) ON DELETE CASCADE;

You create table T1 and then turn on the schema flexibility of the table. You test the schema flexibility of table T1 by inserting a new record. A new column F is automatically created and a record is inserted:

 CREATE COLUMN TABLE T1 (C INT);
 ALTER TABLE T1 ENABLE SCHEMA FLEXIBILITY;
 INSERT INTO T1 (C, F) VALUES (1, 'NVAR1');

You create table T2 with a fuzzy search index and fuzzy search mode. You then switch off the fuzzy search index of COL1 and finally reset fuzzy search mode of COL2.

 CREATE COLUMN TABLE T2 (KEY INT, COL1 VARCHAR(10) FUZZY SEARCH INDEX ON, COL2 NVARCHAR(10) FUZZY SEARCH MODE 'postcode');
 ALTER TABLE T2 ALTER (COL1 VARCHAR(10) FUZZY SEARCH INDEX OFF);
 ALTER TABLE T2 ALTER (COL2 NVARCHAR(10) FUZZY SEARCH MODE NULL);