SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements

HP NonStop SQL/MX Reference Manual—540440-003

2-12

Syntax Description of ALTER TABLE

table

specifies the name of the table to alter. See Database Object Names on page 6-12.

ADD [COLUMN] column-definition

adds a column to table.

The clauses for the column-definition are specified as:

column-name

specifies the name for the new column in the table. Column-name is an SQL

identifier. column-name must be unique among column names in the table. If

the column name is an SQL/MX reserved word, you must delimit it by

enclosing it in double quotes. For example: mycat."sql".myview. See

Identifiers on page 6-52.

data-type

specifies the name and data type for the new column in the table.

data-type is the data type of the values that can be stored in column. See

Data Types on page 6-16.

DEFAULT default

specifies a default value for the column. The added column must have a

default value. You can declare the default value explicitly by using the

DEFAULT clause or you can enable null to be used as the default by omitting

both the DEFAULT and NOT NULL clauses. If you omit the DEFAULT clause

and specify NOT NULL, NonStop SQL/MX returns an error. For existing rows

of the table, the added column takes on its default value.

If you set the default to the datetime value CURRENT_DATE,

CURRENT_TIME, or CURRENT_TIMESTAMP, NonStop SQL/MX uses

January 1, 1 A.D. 12:00:00.000000 as the default date and time for the existing

rows.

For any row that is added after the column is added, if no value is specified for

the column as part of the add row operation, the column will receive a default

value based on the current timestamp at the time the row is added.

If you set the default value to USER, CURRENT_USER, or SESSION_USER,

NonStop SQL/MX uses " " (blank) as the default value for the existing rows.

For any row that is added after the column is added, if no value is specified for

the column as part of the add row operation, the column will receive the current

Guardian user ID for its value. See DEFAULT Clause on page 7-2.