When working with an Oracle database, you may encounter an instance where you need to add a column to an existing table.
You might want to add a column to an existing table in Oracle for many reasons. For example:
- To store additional data for each row in the table
- To update the table to include new data that wasn’t available when the table was first created
- To add a column based on computation from other columns
- Add a column that stores a value referenced by other tables, such as a foreign key
This tutorial will show you how to use the ALTER TABLE ADD COLUMN command to add single or multiple columns to an existing database table.
Oracle ALTER TABLE ADD COLUMN Statement
The following shows a comprehensive syntax of the ALTER TABLE ADD COLUMN statement in Oracle databases:
ADD column_name datatype [DEFAULT default_value] [NULL|NOT NULL] [CONSTRAINT constraint_name] [column_constraint_clause];
Swap table_name with the table’s name to which you wish to add the column(s).
The column_name specifies the name of the column you wish to add to the table. This is similar to column definition during table creation.
During column definition, you can specify column properties, such as data type, default, values, NULL status, and column constraints.
If you want to add multiple columns to a table in Oracle, you can use multiple ADD clauses in the ALTER TABLE statement.
ADD column1_name datatype1 [DEFAULT default_value1] [NULL|NOT NULL] [CONSTRAINT constraint1_name] [column1_constraint_clause],
ADD column2_name datatype2 [DEFAULT default_value2] [NULL|NOT NULL] [CONSTRAINT constraint2_name] [column2_constraint_clause],
...
ADD columnN_name datatypeN [DEFAULT default_valueN] [NULL|NOT NULL] [CONSTRAINT constraintN_name] [columnN_constraint_clause];
Let us look at examples of how we can use the ALTER TABLE ADD COLUMN statement.
Example
Consider the following statement that creates a table to store blog information:
id NUMBER(10) PRIMARY KEY,
title VARCHAR2(255) NOT NULL,
content CLOB NOT NULL,
author VARCHAR2(255) NOT NULL,
date_posted DATE NOT NULL,
category VARCHAR2(255) NOT NULL
);
This statement creates a table named blog_posts with the following columns:
- id: This column stores a unique identifier for each blog post. It has a data type of NUMBER with a maximum precision of 10 digits, and it is the primary key for the table.
- title: This column stores the title of each blog post. It has a data type of VARCHAR2 with a maximum length of 255 characters, and it cannot store NULL values.
- content: This column is used to keep the content of each blog post. It has a CLOB data type, which stands for “Character Large Object”, and can be used to store large amounts of text data. It cannot store NULL values.
- author: This column stores the author of each blog post. It has a data type of VARCHAR2 with a maximum length of 255 characters, and it cannot store NULL values.
- date_posted: This column stores the date and time when each blog post was published. It has a data type of DATE, which holds the date and time in a specific format. It cannot store NULL values.
- category: This column stores the category or topic of each blog post. It has a data type of VARCHAR2 with a maximum length of 255 characters, and it cannot store NULL values.
This CREATE TABLE statement creates a table with the necessary columns to store blog post information, including the title, content, author, date and time posted, and category for each blog post.
Oracle Add a Single Column
Suppose we wish to add a column to store the number of views for a given post. We can use the ALTER TABLE ADD COLUMN as shown below:
This query uses the ALTER TABLE statement in Oracle to add a new column to an existing table.
In this case, the ALTER TABLE statement adds a new column named post_view to the blog_posts table. The post_view column has a data type of number and cannot store NULL values.
After this statement is executed, the blog_posts table will have an additional column named post_view that can store the number of views for each blog post. The database engine will add the column at the end after all the existing columns.
Conclusion
In conclusion, we can use the ALTER TABLE ADD COLUMN command to add a new column to an existing table. This statement can be convenient if you want to add additional data to an existing table or if you need to modify the table’s structure to store new data that wasn’t available during table initialization.
