Adding a New Column to a Table (Embedded SQL Guide)
Go Up to Altering a Table
To add another column to an existing table, use ALTER TABLE. A table can only be modified by its creator. The syntax for adding a column with ALTER TABLE is:
EXEC SQL ALTER TABLE name ADD colname <data_type> colconstraint [, ADD colname data_type colconstraint ...];
For example, the following statement adds a column, EMP_NO, to the EMPLOYEE table:
EXEC SQL ALTER TABLE EMPLOYEE ADD EMP_NO EMPNO NOT NULL; EXEC SQL COMMIT;
This example makes use of a domain, EMPNO, to define a column. For more information about domains, see the Data Definition Guide.
Multiple columns can be added to a table at the same time. Separate column definitions with commas. For example, the following statement adds two columns, EMP_NO, and FULL_NAME, to the EMPLOYEE table. FULL_NAME is a computed column, a column that derives it values from calculations based on other columns:
EXEC SQL ALTER TABLE EMPLOYEE ADD EMP_NO EMPNO NOT NULL, ADD FULL_NAME COMPUTED BY (LAST_NAME || ', ' || FIRST_NAME); EXEC SQL COMMIT;
This example creates a column using a value computed from two other columns already defined for the EMPLOYEE table. For more information about creating computed columns, see the Data Definition Guide.
New columns added to a table can be defined with integrity constraints. For more information about adding columns with integrity constraints to a table, see the Data Definition Guide.