Adding a New Column to a Table

Go Up to Using ALTER TABLE

The syntax for adding a column with ALTER TABLE is:

ALTER TABLE table ADD <col_def>
<col_def> = col {<datatype>
| [COMPUTED [BY] (<expr>)
| domain}
 [DEFAULT {literal | NULL | USER}]
 [NOT NULL] [<col_constraint>]

 [COLLATE collation]
<col_constraint> = [CONSTRAINT constraint] <constraint_def>
 [<col_constraint>]
<constraint_def> =
PRIMARY KEY
| UNIQUE
 | CHECK (<search_condition>)
 | REFERENCES other_table [(other_col [, other_col …])]
[ON DELETE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {RESTRICT|NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

For the complete syntax of ALTER TABLE, see the Language Reference.

For example, the following statement adds a column, EMP_NO, to the EMPLOYEE table using the EMPNO domain:

ALTER TABLE EMPLOYEE ADD EMP_NO EMPNO NOT NULL;

You can add multiple columns 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 two other columns already defined for the EMPLOYEE table:

ALTER TABLE EMPLOYEE
 ADD EMP_NO EMPNO NOT NULL,
 ADD FULL_NAME COMPUTED BY (LAST_NAME || ', ' || FIRST_NAME);

You can also define integrity constraints for columns that you add to the table. For example, the next statement adds two columns, CAPITAL and ­LARGEST_CITY, to the COUNTRY table, and defines a UNIQUE constraint on CAPITAL:

ALTER TABLE COUNTRY
 ADD CAPITAL VARCHAR(25) UNIQUE,
 ADD LARGEST_CITY VARCHAR(25) NOT NULL;