You use ALTER TABLE to modify an existing base table. You can add columns and their associated indexes, update or delete columns, and add or delete integrity constraints. The value for the reservation of free space which is defined using CREATE SPACE .. PCTFREE is taken into account.
If you are using a CALL DML table, you can only add, update or delete columns and their associated indexes, and update ir delete columns. The restrictions that apply to CALL DML tables are described in the section “Special considerations for CALL DML tables” on "Special considerations for CALL DML tables".
You can also use ALTER TABLE to modify a BLOB table. The restrictions that apply in this case are described in the section “Special considerations for BLOB tables” on "Special considerations for BLOB tables".
You can use the UTILITY MODE pragma to add, change or delete a column in a table (ADD without ADD INDEX, ALTER, DROP). When you activate the pragma (UTILITY MODE ON), the associated statement is performed outside a transaction like a utility statement. This suppresses normal transaction logging for the corresponding statement and thus makes it possible to accelerate performance considerably when modifying large data volumes. However, if an error occurs, it is not possible to roll back the statement. The space containing the base table to be changed is defective and must be repaired (see section "UTILITY MODE pragma").
You cannot use ALTER TABLE to change the table type. You can change the table type by means of the UTILITY statement MIGRATE (see the “ SQL Reference Manual Part 2: Utilities”).
The BASE_TABLES view in the INFORMATION_SCHEMA provides you with information on which base tables have been defined (see chapter "Information schemas").
The current authorization identifier must own the schema to which the base table belongs.
ALTER TABLE table
{
ADD [COLUMN] column_definition ,...
[ADD INDEX index_definition ,... [USING SPACE space ]] |
ALTER [COLUMN] column action [, column action ] ...
[USING FILE exception_file [PASSWORD password ]] |
DROP [COLUMN] column,... { CASCADE | RESTRICT } |
ADD [CONSTRAINT integrity_constraint_name ] table_constraint
[,[CONSTRAINT integrity_constraint_name ] table_constraint ],... |
DROP CONSTRAINT integrity_constraint_name { CASCADE | RESTRICT }
}
action ::=
{
DROP DEFAULT |
SET data_type [CALL DML call_dml_default ] |
SET default
}
index_definition ::= index ({ column [LENGTH length ]},...)
default ::= DEFAULT
{
alphanumeric_literal
national_literal
numeric_literal
time_literal
CURRENT_DATE
CURRENT_TIME(3)
LOCALTIME(3)
CURRENT_TIMESTAMP(3)
LOCALTIMESTAMP(3)
USER
SYSTEM_USER
NULL
REF( table )
}
table
Name of a base table.
ADD [COLUMN] column_definition,...
Adds new columns to the base table. The new columns are added after the existing columns. column_definition defines the columns, see section "Column definitions".
If column_definition contains a default value other than NULL, this default value is inserted into every existing record of the table; this could require some time.
No primary key must be defined in column_definition.
An authorization identifier which possesses table privileges for the underlying base table automatically obtains the corresponding privileges for the newly added columns.
If you wish to add a FOR REF column, it does not make sense to use the FOR REF clause for the initial column definition, since this would cause the default value for the REF column to be entered in each row. A more efficient option, particularly with respect to memory requirements, would be to define the column initially with the data type CHAR(237). In this case each row will be assigned the NULL value. The column can then be modified using ALTER COLUMN column SET DEFAULT REF(table). This does not affect any row entries made up to this point.
ADD INDEX index_definition
Definition of one or more indexes for the newly inserted columns.
The rules and referential constraints of the CREATE INDEX statement apply for the index definition, see section "CREATE INDEX - Create index".
index
Name of the new index.
column
Name of the column in the base table you want to index. Only columns which
are specified in the ADD COLUMN clause may be specified.
LENGTH length
Indicates the length up to which the column is to be indexed.
LENGTH length omitted:
The column in its entirety in bytes is indexed.
USING SPACE space
Name of the space in which the index or indexes is/are to be stored.
The space must already be defined for the database to which the table belongs. The current authorization identifier must own the space.
USING SPACE space omitted:
The index is stored in the space for the base table. In the case of a partitioned table, the index is stored in the space for the first partition.
ALTER [COLUMN] column
column is the name of the column to be modified.
Modifications of the column are performed in the following order:
DROP DEFAULT
SET data_type
SET default
You can use one and the same modification type only once for a column.
DROP DEFAULT
Deletes the default (SQL default value) for the column.
The underlying base table must not be a CALL DML table.
SET data_type
New data type of the column.
The column whose data type is to be changed must not be column of a primary key. In CALL DML only tables, the column of a primary key can also be specified.
The column may not be used in views, indexes, integrity constraints, and routines.
You can also change the data type of a multiple column. The data type may not be VARCHAR or NVARCHAR. When a data type is changed to a multiple column data type, SESAM/SQL assigns the position number 1 to the first column element. The number of column elements corresponds to the dimension of the new data type.
An atomic column can contain the multiple column data type and vice versa. In this case, SESAM/SQL considers the atomic value to be the same as the value of a multiple column with dimension 1.
The original column data type can only be modified to certain target data types. The table below illustrates which original data types can be combined with which new data types, and which combinations are not, or are only partially, permitted:
Original | New data | New data | New data | New | New | New | New | New | New data |
INTEGER | REAL | VARCHAR | CHAR | NVARCHAR | NCHAR | DATE | TIME(3) | TIMESTAMP(3) | |
INTEGER | yes | yes 1 | no | yes | no | yes 1 | no | no | no |
REAL | yes | yes | no | yes | no | yes | no | no | no |
VARCHAR | no | no | yes 2 | no | no | no | no | no | no |
CHAR | yes | yes 1 | no | yes | no | yes4 | yes 1 | yes 1 | yes 1 |
NVARCHAR | no | no | no | no | yes 3 | no | no | no | no |
NCHAR | yes | yes | no | yes4 | no | yes | yes | yes | yes |
DATE | no | no | no | yes | no | yes | yes | no | no |
TIME(3) | no | no | no | yes | no | yes | no | yes | no |
TIMESTAMP(3) | no | no | no | yes | no | yes | no | no | yes |
Table 49: Permitted and prohibited combinations for data type modifications
1A column may be changed to the numeric data types REAL, DOUBLE PRECISION, and FLOAT or to the time data types
DATE, TIME, and TIMESTAMP if the fundamental base table is an SQL table
2A column of the data type VARCHAR may only be changed to the new data type with
new_length >= old_length. The other data types may not be changed to the data type VARCHAR and vice versa.
3)A column of the data type NVARCHAR may only be changed to the new data type NVARCHAR with
new_length >= old_length. The other data types may not be changed to the data type NVARCHAR and vice versa.
4) A code table not equal to _NONE_ must be defined for the database.
SESAM/SQL converts all values in column to the new data type row by row. In the case of multiple columns, SESAM/SQL converts the significant values of all variants whose position number is smaller than or equal to the new data type dimension. This means that it is possible that an element’s position may change within the
multiple column: If the result of converting a column is the NULL value, all following elements whose position number is smaller than or equal to the new data type
dimension are shifted to the left and the NULL value is appended after them.
The same rules apply (except for CHAR <-> NCHAR) when converting a column value as when converting a value by means of the CAST expression (see section "Rules for converting a value to a different data type"). When a column value is converted from CHAR to NCHAR and vice versa, the same rules apply as for the transliteration of a value by the TRANSLATE expression,
CATALOG_DEFAULT being used in the USING clause (see the section
"TRANSLATE() - Transliterate / transcode string").
These rules also apply for the conversion of the column element value of a multiple column.
If a conversion error occurs, an error message or alert is issued.
The rounding of a value does not represent a conversion error.
Example
A column of NUMERIC data type is changed to the data type INTEGER.
SESAM/SQL converts the original column value 450.25 to 450 without issuing
an alert.
When conversion errors occur, SESAM/SQL differentiates between truncated
strings, truncated column elements and non-convertible values:
truncated strings
A column with CHAR or NCHAR data type is to be changed to a new CHAR or
NCHAR data type respectively with shorter length. Affected column values
which are longer than the new value are truncated to the length of the new data
type. If characters which are not spaces are removed, SESAM/SQL issues an
alert.Example
The value 'cust_service' in a column which is of alphanumeric data type
CHAR(12) or national data type NCHAR(12) is to be converted to data type
CHAR(6) or NCHAR(6) respectively. The original column value is replaced
by the value 'cust_s'. SESAM/SQL issues an alert.truncated column elements
A multiple column contains at least one column element whose position number
is greater than the dimension of the new data type and which contains a
significant value not equal to NULL.Example
A multiple column of alphanumeric data type (7) CHAR (20) or national data
type (7) NCHAR (20) is to be converted to the data type (5) CHAR (20) or
(5) NCHAR (20) respectively. In some table rows, all 7 elements of the
multiple row contain an alphanumeric value.Non-convertible values
For certain column values, a change of data type results in the loss of values
with an error message (data exception).Examples
The value of an original column of numeric data type is too large for the
target numeric data type.Example
The value 9999 in an INTEGER column is to be converted to the data
type NUMERIC(2,0).A column of alphanumeric data type CHAR or national data type NCHAR is
converted to a numeric data type. The original value of the column cannot
be represented as numeric value.Example
The value 'Otto' in a column with alphanumeric data type CHAR(4)or
national data type NCHAR(4) is to be converted to the data type
INTEGER.The length of the value in an originally numeric column or in a column with
a time data type is too large for the alphanumeric target data type CHAR or
the national target data type NCHAR respectively.Example
The value 9999 in a column of data type INTEGER is to be converted
to the alphanumeric data type CHAR(2) or national data type NCHAR(2)
respectively.
If the column definition for column contains a default, the new data type may not contain a dimensional specification.
If the specified SQL default value is an alphanumeric, national, numeric or time literal, it is converted to the new data type. The conversion must not result in a
conversion error. If the specified SQL default value is a time function, a iteral or the NULL value, it is not changed.
After conversion, the SQL default value for the new data type must conform to the assignment rules for default values (see section "Default values for table columns" ).
CALL DML call_dml_default
Changes the non-significant value of column in a CALL DML table. May only be
specified for CALL DML tables
call_dml_default corresponds to the non-significant attribut value in
SESAM/SQLVersion 1.x.
You specify call_dml_default as an alphanumeric literal.
CALL DML call_dml_default not specified:
If the data type modification applies to the column in a CALL DML/SQL table,
column retains the non-significant attribute value which was assigned to it during
column definition.
If the data type modification applies to a column of a CALL DML only table, i.e.
a table with “old“ attribute formats from SESAM versions < V13.1, column is
assigned the following non-significant attribute value:
space if the column data type is alphanumeric
digit 0 if the column data type is numeric
SET default
Defines a new SQL default value for the column.
The underlying base table must not be a CALL DML table.
column cannot be a multiple column.
default must conform to the assignment rules for default values (see section "Default values for table columns").
The default is evaluated when a row is inserted or updated and the default value is used for column.
USING FILE exception_file [PASSWORD password]
Defines the name of the exception file. exception_file must be specified as an
alphanumeric literal.
SESAM/SQL creates or uses the exception file only if a column conversion
performed using SET data_type results in one or more conversion errors (see
"ALTER TABLE - Modify base table").
If an exception file is specified, a statement which results in a conversion error is continued. SESAM/SQL issues an alert and replaces the original column values by new values in the affected base table:
truncated strings are replaced by the corresponding truncated value.
non-convertible values are replaced by the NULL value.
column items in a multiple column whose position number is larger than the new data type dimension are truncated.
SESAM/SQL logs the original column values and truncated column elements
together with the associated alert or error message in the exception file.
Even when UTILITY MODE is switched ON, a statement which results in a
conversion error is not interrupted. The space which contains the base table to be updated remains intact.
For a detailed description of the exception file and its contents, see section
"Exception file of SQL statement ALTER TABLE".
PASSWORD password
BS2000 password for the error file. You must specify password as an
alphanumeric literal.
password can be specified in several different ways:
'C''string'''string contains four printable characters.'X''hex_string'''hex_string contains eight hexadecimal characters.'n'n is an integer from - 2147483648 through + 2147483647.
USING FILE exception_file not specified:
If a column conversion performed using SET data_type results in a conversion error, SESAM/SQL does not log the affected column values or column elements in an exception file.
Strings are truncated to the length of the new data type and SESAM/SQL issues an alert.
If conversion errors occur because values cannot be converted or column elements have to be truncated, SESAM/SQL aborts the associated statement and issues an error message.
DROP [COLUMN] column,... {CASCADE, RESTRICT}
Deletes one or more columns and associated indices in the base table.
column is the name of the column to be deleted. You can only specify each column name once.
No primary key may be defined for column.
You must not specify all columns in the base table.
Deleting a column revokes the column privileges UPDATE and FOREIGN KEY... REFERENCES for this column from the current authorization key. If these privileges have been passed on, then the passed on privileges are also withdrawn.
In addition, deleting the column also deletes all views where column was used in the view definition as well as all views whose definitions contain the name of such a “higher level“ view.
The arrangement of the remaining columns in a table can change: if deleting a column results in a gap, all following columns are shifted to the left.
CASCADE
Deletes the specified column(s) and associated indices.
The integrity constraints of other tables or columns which use column are also
deleted. All routines which reference this column directly or indirectly are deleted.
You cannot use the UTILITY MODE pragma. If you activate the UTILITY MODE, an error message is output and the statement is aborted.
RESTRICT
Deletion of a column is restricted:
The column cannot be deleted if it is used in a view definition or a routine. You may only define an index for the column to be deleted if none of the remaining columns in the base table is named in the affected index definition. The same applies to the integrity constraints.
The UTILITY MODE pragma can be activated when no index is defined for the
column.
ADD CONSTRAINT clause
Adds integrity constraints to the base table.
CONSTRAINT integrity_constraint_name
Assigns a name to the integrity constraint. You can qualify the name of the integrity constraint with a database and schema name. The database and schema name must be the same as the database and schema name of the base table.
CONSTRAINT integrity_constraint_name omitted:
The integrity constraint is assigned a name according to the following pattern:
UN integrity_constraint_number
FK integrity_constraint_number
CH integrity_constraint_number
where UN stands for UNIQUE, FK for FOREIGN KEY and CH for CHECK.
integrity_constraint_number is a 16-digit number.
table_constraint
Specifies an integrity constraint for the table. table_constraint cannot define a primary key constraint.
DROP CONSTRAINT integrity_constraint_number {CASCADE, RESTRICT}
Deletes the integrity constraint integrity_constraint_name.
integrity_constraint_number may not name a primary key constraint.
CASCADE
If integrity_constraint_name is a uniqueness constraint, and if the referential
constraint of another table references the column(s) for which
integrity_constraint_name was defined, the referential constraint of the other table is also implicitly deleted.
RESTRICT
You must not delete a uniqueness constraint on a column if a referential constraint on another table references this column(s).
Special considerations for CALL DML tables
The ALTER TABLE statement for CALL DML tables must take the following restrictions into account:
Only the ADD [COLUMN], DROP [COLUMN] and ALTER [COLUMN] clause are permitted with SET data_type.
A newly inserted column must include a CALL DML clause.
Only the data types CHAR, NUMERIC, DECIMAL, INTEGER and SMALLINT are permitted.
No integrity constraint or default value (DEFAULT) can be defined for the column.
The column name must be different to the integrity constraint name of the table constraint since this name is used as the name of the compound primary key.
A column’s data type in a CALL DML table may only be changed to the data type of a CALL DML/SQL table. In particular, a CALL DML table’s data type must not be changed to an “old attribute format”, i.e. to an attribute format of SESAM version <13.1.
An “old attribute format” in a CALL DML only table can be changed to the following data types:
CHAR with new_length >= old_length
NUMERIC with old_fraction=new_fraction
DECIMAL with old_fraction=new_fraction
INTEGER
SMALLINT
You can assign a new non-significant attribute value for columns in a CALL DML table. You may not change the symbolic attribute name.
If a data type modification results in a value in a CALL DML column receiving the nonsignificant attribute value, the value of the column in question is considered to be nonconvertible. If no exception file was specified, SESAM/SQL issues an error message and aborts the statement. If an exception file is specified, SESAM/SQL reacts as in the case of non-convertible values in an SQL table (see "ALTER TABLE - Modify base table").
You can neither use the ALTER [COLUMN] clause nor the DROP [COLUMN] clause to change the table type. Even if the columns in a CALL DML only table have been changed or deleted so that none of the columns contains an “old attribute format”, the “CALL DML only” table type remains unchanged. You can change the table type by means of the UTILITY statement MIGRATE (see the “ SQL Reference Manual Part 2: Utilities”).
Converting “old” attributes in a CALL DML only table
The attribute of a CALL DML only table has no explicit type: the type is simply specified by the way the table is saved. The user must interpret the values correctly.
You cannot use ALTER COLUMN to change the type, but only to transfer it to the specified type. When you do this, values of the corresponding type are transferred and those of different types are rejected (SQLSTATE 22SA5).
You should therefore only specify the appropriate type. Conversion to another type is only possible if you use a second ALTER COLUMN and specify the new data type.
For example, a binary value can only be changed to INTEGER, SMALLINT. After a second ALTER COLUMN you can also convert it to NUMERIC, DECIMAL and CHAR.
ALTER COLUMN reads each value and prepares it in accordance with its definition in the CALL DML table. Alignment, fill bytes etc. are not taken into account. However, no conversion is performed. After that, a check is performed to determine whether the read value corresponds to the specified format or not.
Since the attributes of the CALL DML only table also contain values of different types, it is advisable to always specify USING FILE exception_file for “old” attributes when using ALTER COLUMN. All inappropriate values are then entered in the exception file.
If no exception file is present, ALTER COLUMN aborts when the first inappropriate value is encountered.
Special considerations for BLOB tables
You can also use ALTER TABLE to modify a BLOB table. However, certain types of changes may result in the BLOB table becoming inaccessible to CLI calls. The permitted changes and their effects are described below:
Inserting a new column in a BLOB table does not affect the execution of CLI calls.
Additional integrity constraints on BLOB tables can be defined using the ADD CONSTRAINT clause without any negative repercussions.
If one of the columns OBJ_NR, SLICE_NR, SLICE_VAL or OBJ_REF is deleted or its type is changed, it will no longer be possible to process BLOB values in CLI functions.
Exception file of SQL statement ALTER TABLE
When you modify a column (ALTER COLUMN), you can specify the name of an exception file. If necessary, you can protect the exception file using a BS2000 password. The exception file is used to store column values for which conversion errors resulted in data loss because of a change of data type.
If you have specified an exception file and conversion errors occur during the modification of the data type, SESAM/SQL sets up the exception file as a SAM file under the DBH user ID if this does not yet exist.
If the exception file is not to be stored on the DBH user ID, preparations must have been made, see section “Database files and job variables on foreign user IDs” in the “ Core manual”.
If an exception file is specified, statements which result in a conversion error are not aborted. SESAM/SQL issues an alert and replaces the original column value by a new value in the affected base table. Depending on the error type, the value is replaced by a truncated value or the NULL value.
SESAM/SQL logs the original column values together with the associated error message or alert in the exception file. If an exception file exists, its contents are not overwritten. SESAM/SQL appends the new entries to the existing entries.
The exception file is not subject to transaction logging. It remains intact, even if the transaction which SESAM/SQL uses to write entries to the exception file is implicitly or explicitly rolled back.
You can display the contents of the exception file using the SHOW-FILE command.
Contents of the exception file
The exception file contains an entry for each logged column value. The entry consists of the corresponding SQL status code and the components which identify the column value within the associated base table.
entry ::=
row_id
column_name [ posno ]
sqlstate
column_value
row_id ::= { primary_key | row_counter }
row_id
Identifies the table rows which contains the column_value.
In tables with primary keys, row_id is the primary key value which uniquely identifies the corresponding row. Its representation in the error file corresponds to the representation of column_value (see under the appropriate information). The same applies to the compound keys.
In tables without primary key, row_id is the counter of the row containing column_value. SESAM/SQL numbers all table rows sequentially. The first row in the table contains the value 1 as row_counter.
row_counter is an unsigned integer.
column_name
Name of the column containing to the column_value. In multiple columns, column_name also contains the position number, in unsigned integer format, of the affected column element. The first element of the multiple column has the position number 1.
sqlstate
SQLSTATE of the associated error message or alert.
column_value
Original column value for which the ALTER TABLE statement resulted in a conversion error.
Depending on the data type of the associated, column_value is represented in the following ways in the exception file:
Data type of column | Representation of column_value in the exception file |
Data type of a CALL DML table | string with a maximum length of |
CHAR | string with a maximum length of |
NCHAR | string with a maximum length of |
INTEGER, SMALLINT, | corresponding numeric literal |
FLOAT, REAL, | corresponding numeric literal |
DATE | Date time literal |
TIME | Time time literal |
TIMESTAMP | Timestamp time literal |
Table 50: Representation of column_value data types
Strings are represented without surrounding single quotes in the exception file.
If the original value is a string which contains double quotes, these are represented as single quotes in the exception file.
Example
The following example shows an exception file which contains the original column values of the base table SERVICE.
The base table SERVICE has the following structure:
SQL CREATE TABLE service
(service_num INTEGER CONSTRAINT service_num_primary PRIMARY KEY,
order_num INTEGER CONSTRAINT s_order_num_notnull NOT NULL,
service_date DATE, ...)
Its entries are the result of conversion errors which were caused by the following statements:
ALTER TABLE service ALTER COLUMN service_price SET NUMERIC(5,2)
USING FILE 'ERR.SERVICE'
Excerpt from the exception file ERR.SERVICE:
row_id column_name sqlstate column_value 2 SERVICE_PRICE 22SA4 1500 3 SERVICE_PRICE 22SA4 1500 4 SERVICE_PRICE 22SA4 1200 5 SERVICE_PRICE 22SA4 1200 . . 11 SERVICE_PRICE 22SA4 1200
When converting SERVICE_PRICE from NUMERIC (5,0) to NUMERIC(5,2), any rows containing the specified primary key will be ignored.
Examples
The following examples demonstrate how to modify various properties of the CUSTOMERS and ORDERS tables:
Add two new columns, CUST_TEL and CUST_INFO, to the CUSTOMERS table.
ALTER TABLE customers
ADD COLUMN cust_tel CHARACTER(25), cust_info CHARACTER(50)
In the CUSTOMERS table, change the data type of the CUST_NUM column.
The original data type was NUMERIC, the new data type is INTEGER.
ALTER TABLE customers
ALTER COLUMN cust_num SET INTEGER
Delete the CUST_INFO column from the CUSTOMERS table.
This is possible only if the CUST_INFO column is not used in any view definition. An index or an integrity constraint can then only be defined for the CUST_INFO column if none of the remaining columns of the base table is specified in the definition.
ALTER TABLE customers
DROP COLUMN cust_info RESTRICT
Add a uniqueness constraint on the CUST_NUM column of the CUSTOMERS table.
ALTER TABLE customers
ADD CONSTRAINT cust_num_unique UNIQUE(cust_num)
Delete the referential constraint between the CUST_NUM column of the ORDERS table and the CUST_NUM column of the CUSTOMERS table. You can look up the names of the integrity constraints used in the TABLE_CONSTRAINTS, REFERENTIAL_CONSTRAINTS and CHECK_CONSTRAINTS views of the INFORMATION-SCHEMA.
ALTER TABLE orders
DROP CONSTRAINT o_cust_num_ref_customers CASCADE
See also
CREATE TABLE