Summary: in this tutorial, you’ll learn how to use the Db2 ALTER TABLE ALTER COLUMN statement to modify column definitions.
Introduction to Db2 ALTER TABLE ALTER COLUMN statement
Sometimes, you may want to modify column definition to cope with the new business requirements such as extend the maximum length of a column or changing the default value of a column to a new one.
Db2 ALTER TABLE ALTER COLUMN allows you to modify the definitions of the existing columns in a table.
The following illustrates the syntax of the ALTER TABLE ALTER COLUMN statement:
1 2 3 | ALTER TABLE table_name ALTER COLUMN column_name modification_type; |
In this syntax:
- First, specify the name of the table which you want to perform the change in the
ALTER TABLEclause. - Second, specify the name of the column that you want to modify.
- Third, add a clause as a modification type. Db2 allows you to change the data type of a column, adjust the column length, and change the default value to a new one.
Db2 ALTER TABLE ALTER COLUMN examples
We’ll use the orders table created in the ALTER TABLE ADD COLUMN tutorial for the demonstration.
1) Modifying the length & data type of column examples
The following example uses the ALTER TABLE ALTER COLUMN statement to change the data type of the created_date column from DATE to TIMESTAMP:
1 2 3 | ALTER TABLE orders ALTER COLUMN created_date SET DATA TYPE TIMESTAMP; |
To view the columns of the orders table, you use the DESCRIBE TABLE command:
1 | DESCRIBE TABLE orders; |
Here is the output:
This statement adds a new column named note to the orders table:
1 2 | ALTER TABLE orders ADD COLUMN note VARCHAR(40); |
To increase the length of the note column to 255, you use the following statement:
1 2 3 | ALTER TABLE orders ALTER COLUMN note SET DATA TYPE VARCHAR(255); |
2) Changing the default value to a new one example
To change the default value of a column to a new value, you use the following syntax:
1 2 3 | ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT new_default_value |
The following example sets the default value of the created_date column to the current timestamp:
1 2 3 | ALTER TABLE orders ALTER COLUMN created_date SET DEFAULT CURRENT_TIMESTAMP; |
In this tutorial, you’ve learned how to use the Db2 ALTER TABLE ALTER COLUMN statement to modify the definition of a column such as data type, length, and default value.