Updated March 6, 2023
Introduction to DB2 alter table
DB2 ALTER TABLE statement is used for adding or modifying the columns to change the definition of the table in the DB2 relational database management system. Many times, due to certain business requirements or enhances in our application, we need to modify the columns, their definitions, structure of the table, their order, and even add one or more columns to store the extra data related to our applications. In this case, we can make the use of ALTER TABLE query statement to add or modify the columns of the existing table. In this article, we will study the usage of ALTER TABLE query statement, its syntax, and the implementation with the help of certain examples.
Syntax
Let us first see the syntax of adding the new columns to the existing table using the ALTER TABLE query statement which is as shown below –
ALTER TABLE
Name of the existing table
ADD COLUMN name of first column to be added data type constraint
ADD COLUMN name of first column to be added data type constraint
...;
We can add one or more than one column to the existing table by making use of the single ALTER TABLE query statement. In the above syntax, we have to mention the “ADD COLUMN” before we specify the name of the new column that we want to add to the table, after which it is necessary to specify the datatype of the column which will help the database to get known about the type of the value that might be stored in the future in this new column. After this, we can specify any constraints that we need to apply to that column such as NOT NULL, auto increment, default value, etc.
If you want to add multiple columns using the same query statement then we will make the use of ADD COLUMN repetitively for as many columns that we have to add and are specified in comma-separated format.
Now, let us have a look at the syntax of ALTER TABLE that is used for modifying the column definition of the existing column of the table in DB2 which is as shown below –
ALTER TABLE name of the table
ALTER COLUMN name of the column
Type of the modification to be made;
In the above syntax, we can modify only one column at a time by using the “ALTER COLUMN ” statement it. After which it is necessary to specify the existing column name that is present in the table whose name is specified just after ALTER TABLE statement. Further, we need to mention the type of modification that we need to make in the column such as setting the default value or changing the data type of the size and range of the field value that it is going to store, etc.
We can even drop a particular column which will lead to deletion of the content as well as the definition and existence of that column completely from the database by using the ALTER TABLE query statement along with the DROP COLUMN statement whose syntax is as given below –
ALTER TABLE name of the table
DROP COLUMN name of the column
Where the name of the column that you are specifying is necessarily present in the table whose name you have specified after writing the ALTER TABLE command and which you wish to drop from the table.
Examples –
Let us consider one table called customer_sales which is created using the following query statement –
CREATE TABLE customer_sales
( customer_id NUMBER(6)
, f_name VARCHAR2(20)
, l_name VARCHAR2(25)
, email_id VARCHAR2(40)
, mobile_number VARCHAR2(20)
, purchase_date DATE
, store_id VARCHAR2(20)
, bill_amount NUMBER(8,2)
, salesman_id NUMBER(6)
, department_id NUMBER(4)
) ;
The execution of the above query statement gives the following output as the result and creates our table successfully –
After that, the table contains the following columns after selecting the data from it as shown below –
Let us first make the use of ALTER TABLE command to drop the column from our table whose name id email_id using the DROP COLUMN clause in the query. Hence, our query statement will look as follows –
ALTER TABLE customer_sales DROP COLUMN email_id;
After dropping the email_id column from our table by executing the above query statement the table now contains the columns as shown in the below image –
Now, let us modify the table and change the column department_id and modify its default value to 1 by using the ALTER TABLE ALTER COLUMN query statement to do so. Our query statement will now become as shown below –
ALTER TABLE customer_sales ALTER COLUMN department_id SET DEFAULT 1;
Now, if we try to add any row value with none of the value specified for our department_id column then it will by default insert the value 1 in the department_id column. For example, let us insert one row in the customer_sales table and check the data of the table. We will insert the row with no department_id column value by making the use of the following query statement –
INSERT INTO customers(customer_id, f_name, l_name, mobile_number , purchase_date , store_id ,bill_amount, salesman_id)
VALUES (100,'KRITIKA','PALAK','[email protected]','9145685245',TO_DATE('17-06-2003','DD-MM-YYYY'),'GROCERIES',24000,NULL);
Now, if we check the contents of the table by using the following query statement –
SELECT * FROM [customer_sales];
The output of the execution of the above query statement is as shown below with department_id column value as 1 as it has been set as the default value of that column using the ALTER TABLE query statement.
Now, let us try to add one column to our customer_sales table using the ALTER TABLE query statement and ADD COLUMN clause to it –
ALTER TABLE customer_sales ADD COLUMN address VARCHAR(250);
Which gives the following output after execution –
Let us retrieve the contents of the table customer_sales now to verify whether the new column has been added or not which gives the following output –
Conclusion
ALTER TABLE query statement can be used in DB2 to modify the existing columns, add the new ones and drop the previous existing columns from the tables that are present in the database.
Recommended Articles
This is a guide to DB2 alter table. Here we discuss the usage of ALTER TABLE query statement, its syntax, and the implementation with the help of certain examples. You may also look at the following article to learn more –
