Db2 ALTER TABLE ADD COLUMN

Summary: in this tutorial, you will learn how to add one or many columns to a table by using the Db2 ALTER TABLE ADD COLUMN statement.

Introduction to Db2 ALTER TABLE ADD COLUMN statement

Because of the new business requirements, you may want to add one or more columns to an existing table.

To add a column to a table, you use the ALTER TABLE ADD COLUMN statement as shown in the following syntax:

ALTER TABLE table_name
ADD COLUMN column_name data_type column_constraint;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table to which you want to add the new column in the ALTER TABLE clause.
• Second, specify the new column including name, data type, and column constraint in the ADD COLUMN clause.

If you want to add many columns to an existing table, you use multiple ADD COLUMN clauses as shown the following syntax:

ALTER TABLE 
	table_name
ADD COLUMN c1 data_type constraint
ADD COLUMN c2 data_type constraint
...;
Code language: SQL (Structured Query Language) (sql)

Note that there is no comma between the ADD COLUMN clauses.

Db2 ALTER TABLE ADD COLUMN examples

Let’s take some examples of using the ALTER TABLE ADD COLUMN statement.

First, create a new table named orders for demonstration.

CREATE TABLE orders (
	order_id INT GENERATED ALWAYS 
		AS IDENTITY NOT NULL,
	created_date DATE NOT NULL,
	PRIMARY KEY(order_id)
);
Code language: SQL (Structured Query Language) (sql)

1) Using Db2 ALTER TABLE ADD COLUMN to add one column example

To add a new column named customer_id to the orders table, you use the following ALTER TABLE ADD COLUMN statement:

ALTER TABLE orders
ADD COLUMN customer_id INT;
Code language: SQL (Structured Query Language) (sql)

To verify the change to the columns of the orders table, you use the DESCRIBE TABLE command:

DESCRIBE TABLE orders;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

2) Using Db2 ALTER TABLE ADD COLUMN to add multiple columns example

The following example adds two new columns named requested_date and order_status to the orders table:

ALTER TABLE orders 
ADD COLUMN requested_date DATE NOT NULL DEFAULT CURRENT_DATE 
ADD COLUMN order_status SMALLINT NOT NULL DEFAULT 0;
Code language: SQL (Structured Query Language) (sql)

Here is the new structure of the orders table:

DESCRIBE TABLE orders;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 ALTER TABLE ADD statement to add one or more columns to a table.