CA-L-Serv Commands › SQL Statements › ALTER TABLE Statement

ALTER TABLE Statement

The ALTER TABLE statement adds a column to or removes a column from an existing database table.

Syntax

LSQL ALTER TABLE tablename [[ADD COLUMN] [ADD_COLUMNcolname] [datatype [[UPPER CASE] 
[NOT NULL ] [DEFAULT value]]] [DROP COLUMN] [colname]]

Parameters

ADD COLUMN

Adds a new column to an existing table.

colname

1‑ to 18‑character name of the column you are adding or dropping.

datatype

Type of data the column can store:

CHAR(length)

Character data, with length being the maximum number of characters. A column of this type must be from 1 through 32000 bytes in length.

DATE

Date indicator of the form yyyy‑mm‑dd; for example, 2001‑08‑31.

DECIMAL(nn,nn)

Decimal data, with the maximum number of digits being 15.

DOUBLE PRECISION

Approximate numeric data, 8‑byte length.

FLOAT(nn)

Approximate numeric data of variable length; mantissa 2‑16 digits, exponent range e‑60 to e60.

HEX(length)

Hexadecimal data, with length as the maximum number of hexadecimal bytes.

INTEGER

32‑bit integer data, with a maximum value of 2147483647.

REAL

Approximate numeric data, 4‑byte length.

SMALLINT

16‑bit integer data with a maximum value of 32767.

TIME or TIME(nn)

Time indicator of the form hh:mm:ss; for example, 13:21:53.

TIMESTAMP or TIMESTAMP(nn)

Date/time indicator, the format being a combination of the DATE and TIME formats.

Note: The DATE, TIME, and TIMESTAMP data types are stored as unsigned packed decimal numbers. When inserting, updating, deleting, or searching for these values, specify the data type with the value. For example:
WHERE CLOSE_DATE = DATE '2001‑08‑31'

DEFAULT value

Value to be set for this column if an INSERT statement does not provide one. The default value can be a character string or a numeric value; however, it must be compatible with the data type of the column.

DROP COLUMN

Drops a column from an existing table.

NOT NULL

Indicates that the column cannot contain a null value.

tablename

Name of the relational table to which you are adding or from which you are dropping a column.

UPPER CASE

Converts the entries in the column to upper case characters.

Notes:

• Issue an ALTER TABLE statement with an ADD COLUMN clause to add a column to an existing relational table.
• Issue an ALTER TABLE statement with a DROP COLUMN clause to drop a non‑primary key column from an existing relational table; you cannot drop a primary key column from an existing table.
• You cannot add a new column to and cannot delete an existing column from a table within the same ALTER TABLE statement.
• You can define up to 250 columns per table.
• You can use host variables for tablename or colname.
• If you do not specify a value for the DEFAULT parameter for a new column, the column contains binary zeros.
• When a column is added, it is positioned to the right of the right‑most column.
• When a column is dropped, the columns to the right of the dropped column are shifted to the left by the number of bytes formerly occupied by the dropped column.

Example

To add a 2‑character column that is named RET_CODE to the APPLICATIONS table, issue this LSQL command:

LSQL ALTER TABLE APPLICATIONS ADD COLUMN RET_CODE CHAR(2)
  DEFAULT   '0'