In this article, we are going to talk about the ALTER TABLE statement and different ways to use it in Oracle. The syntax provided in this article will work for any version of Oracle as well as with SQL Server, MySQL, and PostgreSQL.
Oracle database offers a cutting-edge experience in storing and retrieving all kinds of related information. It’s available both locally and in cloud environments providing outstanding flexibility and reliability. You can use it to run online transaction processing, data warehousing, and mixed database workloads. A lot of world-famous companies like Netflix, LinkedIn, eBay, etc. have chosen Oracle for their tech stacks.
ALTER TABLE Statement
This article explores in detail the Oracle ALTER TABLE command in Oracle that changes the definition of an existing table.
There are two main ways to use the ALTER TABLE statement in Oracle:
- By executing the Oracle ALTER TABLE query
- By using special IDEs, for example, dbForge Studio for Oracle
ALTER TABLE Syntax in Oracle
Let’s start with the basics. Any database specialist has to deal with lots of commands. The Oracle ALTER TABLE statement is among the basic ones.
The Oracle ALTER TABLE syntax is used in all Oracle versions (including 11g) to add, modify, or drop/delete columns in a table.
ALTER TABLE Example
In order to take a better look at how the syntax works, we will create a new table in Oracle using the CREATE TABLE statement:
CREATE TABLE ot.customers(
person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
);We will be using the created table to demonstrate how to add, rename, modify, and delete columns.
ALTER TABLE ADD COLUMN in Oracle
ADD COLUMN is one of the Oracle ALTER TABLE statement variations that is used to add a column if not exist. The default column position in this case will be at the end of the table. Unfortunately, the syntax does not allow adding columns
To ADD A COLUMN in a table, the basic ALTER TABLE syntax is:
ALTER TABLE table_name ADD column_name;
Here’s an example with the existing customers table. Let’s add a new column, phone_number:
ALTER TABLE customers ADD phone_number varchar2(50) NOT NULL;
How to Add Multiple Columns
To ADD MULTIPLE COLUMNS to an existing table, the Oracle ALTER TABLE syntax is as follows:
ALTER TABLE table_name
ADD (column_1,
column_2,
...
);
Oracle ALTER TABLE RENAME Column
To change a column name in Oracle, use the ALTER TABLE RENAME COLUMN statement. This works for any Oracle version, including 11g and 12c.
To modify a name of a column in an existing table, the Oracle ALTER TABLE syntax is:
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
Here’s an example of renaming the first_name column to customer_first_name:
ALTER TABLE table_name RENAME COLUMN first_name TO customer_first_name;
Oracle ALTER TABLE MODIFY for Column
To MODIFY a column in an existing table, the Oracle ALTER TABLE syntax is:
ALTER TABLE customers MODIFY column_name column_type;
ALTER TABLE MODIFY allows modifying the constraints such as primary key, column size, type, its default value, length, nullability, etc.
Here is an example of modifying the first_name column. In particular, the change will make the column not accept the NULL values.
ALTER TABLE customers MODIFY first_name varchar2(100) NOT NULL;
ALTER TABLE DROP Column
There are cases when you need to remove an existing column from a table. That is where ALTER TABLE DROP comes in handy.
To delete a column in an existing table, the Oracle drop column syntax is:
ALTER TABLE table_name DROP COLUMN column_name;
Here is an example of dropping the customer_name column:
ALTER TABLE customers DROP COLUMN phone_number;
How to DROP Multiple Columns
ALTER TABLE DROP statement not only allows removing one column but also dropping multiple columns in Oracle:
ALTER TABLE customers
DROP COLUMN (cutomer_first_name, last_name, phone_number);Using the dedicated IDE–dbForge Studio for Oracle
Nowadays, the most precious human resource is time, and manual coding is among the most time-consuming tasks. Is it really necessary to proceed with scripts manually?
In fact, there are a lot of helpful multifunctional tools that will do all the routine instead of you. One of the most useful ones is dbForge Studio for Oracle, an Oracle GUI client that allows you to add, modify, and drop table columns visually, i.e. you don’t need to write any code at all.
ALTER TABLE ADD COLUMN in Oracle
So, to modify a table in dbForge Studio for Oracle, right-click a required table in Database Explorer, and select Edit Table.
On the Columns tab, right-click anywhere in the columns list grid and select New Column.
Next, just type the column name and dbForge Studio will automatically suggest column types for it, but you can also select another data type in the Data Types drop-down list of a column. Also, you can specify should the column would accept null values and set the default value for a column.
We have created two new columns in the EMP table, EMAIL and CITY.
Next, all we have to do is to click Apply Changes. Also, we can click Script Changes and select and take a look at the ALTER TABLE script that we would normally write word by word. You can make some required changes, and apply them by executing the script against the Oracle database or save it for future use.
If you need to create or delete a table, you can refer to the How to create and drop table in Oracle with dbForge article.
Oracle ALTER TABLE RENAME Column
Next, let’s go back to Table Editor and modify the City column to County. All you need to rename a column to type the new name instated of the old one.
Here is the generated ALTER TABLE RENAME script.
To learn how to rename Oracle tables, you can refer to the How to rename table name in Oracle blog post.
Oracle ALTER TABLE MODIFY
Table editor also allows modifying the column properties without manual coding. For example, to make our County column not accept NULL values. For this, we need to just check the Not Null option in Table Editor.
Let’s take a glimpse at the script changes again.
ALTER TABLE DROP
Finally, to delete columns from the table in dbForge Studio for Oracle, right-click a column, and select Remove column.
Here is the ALTER TABLE DROP script for deletion of both columns we created, EMAIL and COUNTY.
Conclusion
We have explored two main approaches to altering tables and adding columns in Oracle. Using the Oracle ALTER TABLE column statement is convenient if you need to work closely with the script. However, if you want to boost productivity and make the processes faster and more efficient, you might try compatible multifunctional tools with built-in capabilities like dbForge Studio for Oracle. You can download it from our website.
