ALTER TABLE Statement

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]]

Operand	Explanation
ADD COLUMN	The clause used to add a new column to an existing table.
colname	The 1‑ to 18‑character name of the column you are adding or dropping.
datatype	The type of data the column can store. The data type can be one of the following: CHAR(length) ‑ character data, with length being the maximum number of characters. A column of this type must be between 1 and 32000 bytes in length. DATE ‑ a 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) ‑ a time indicator of the form hh:mm:ss; for example, 13:21:53. TIMESTAMP or TIMESTAMP(nn) ‑ a 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, you must specify the data type along with the value. For example: WHERE CLOSE_DATE = DATE '2001‑08‑31'
DEFAULT value	The value to be set for this column if an INSERT statement does not provide one. The default value can be either a character string or a numeric value; however, it must be compatible with the data type of the column.
DROP COLUMN	The clause used to drop a column from an existing table.
NOT NULL	Indicates that the column cannot contain a null value.
tablename	The name of the relational table to which you are adding or from which you are dropping a column.
UPPER CASE	Converts 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 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 will contain 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 called RET_CODE to the APPLICATIONS table, issue this LSQL command:

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

Tell Technical Publications how we can improve this information