Toad World Blog

How to add a column to table in Oracle Database

Jun 14, 2022 3:00:00 AM by Deepak Vohra

This article explains how to add a column to table in an Oracle database. After creating a database table, it may be needed to add additional columns as the table design evolves based on data requirements, making it necessary to add a column to table.

Preliminary Setup

This article is based on Oracle Database 19c; for earlier versions such as 12c some differences may exist. As a preliminary setup before we learn how to add a column to table, install Toad for Oracle, create an instance of Oracle Autonomous Database 19c (or other) and connect to the database instance, all of which is discussed in How to download Toad for Oracle.

Grant User Privileges

The first step to add a column to table is to create a new user or use an existing user. The preexisting user ADMIN is used in this article. Grant the user privileges to alter any table.

GRANT ALTER ANY TABLE TO ADMIN;

Further, create a new table t1 to which a column is to be added.

CREATE TABLE t1(c2 VARCHAR2(255));

Add a column

Add a new column to table with the ALTER TABLE… ADD statement. Set the column properties in the same command itself. As an example, add column c1 of data type INT with default value of 1. Set the column as non-nullable with the NOT NULL clause. Set the c1 column as the primary key with the PRIMARY KEY clause.

ALTER TABLE t1 ADD c1 INT DEFAULT 1 NOT NULL PRIMARY KEY;

Run the statement in Toad for Oracle, and the output Table altered shown in Figure 1 indicates that the new column was added.

add a column to table 1

Figure 1. Table altered to add a new column

Rename a column

After adding a new column to table, it may be necessary to change column name, as an example from c1 to col1. Use the ALTER TABLE … RENAME COLUMN …TO.. statement to rename the column.

ALTER TABLE t1 RENAME COLUMN c1 TO col1;

As the output in Figure 2 shows, the table gets altered to rename the column.

add a column to table 2Figure 2. Column renamed

Describe the table t1 and it should list the renamed column:

DESC t1;

TABLE t1

Name                                     Null?   Type                      

----------------------------------------- -------- ----------------------------

C2                                                 VARCHAR2(255)              

COL1                                     NOT NULL NUMBER(38)                             

A table must have at least one column

When adding/dropping columns, it should be kept in consideration that a table must have at least one column. To demonstrate, run a command to drop both the columns, c2 and col1.

ALTER TABLE t1 DROP COLUMN c2;

ALTER TABLE t1 DROP COLUMN col1;

An error gets generated indicating that all columns in a table cannot be dropped.

>> ALTER TABLE t1 DROP COLUMN col1

Error at line 2

ORA-12983: cannot drop all columns in a table

Change column properties

If after adding a column you need to change its properties, use the MODIFY clause in the ALTER TABLE statement. As example, change the type of col1 to NUMBER(10), and set its default value to 2.

ALTER TABLE t1 MODIFY col1 NUMBER(10) DEFAULT 2;

The column properties get changed, as shown in Figure 3.

add a column to table 3Figure 3. Column properties changed

While changing column properties, it should be kept in consideration that while some column attributes may be reapplied, such as setting the column type to what it already is and setting the default value to the current default value, other column attributes cannot be reapplied.

As an example, set the column type for col1 to NUMBER(10), and default value to 2, and the ALTER TABLE .. MODIFY commands runs OK:

ALTER TABLE t1 MODIFY col1 NUMBER(10);

ALTER TABLE t1 MODIFY col1 DEFAULT 2;

Next, modify column col1 to make it the primary key, which it already is:

ALTER TABLE t1 MODIFY col1 PRIMARY KEY;

This time, an error message is generated:

>> ALTER TABLE t1 MODIFY col1 PRIMARY KEY

Error at line 10

ORA-02260: table can have only one primary key

Similarly, modify column col1 to make it NOT NULL, which it already is. Again, an error message is generated:

>> ALTER TABLE t1 MODIFY col1 NOT NULL

Error at line 1

ORA-01442: column to be modified to NOT NULL is already NOT NULL

A column that is NULLable cannot be made NULLable again, or an error message is generated. As example, make the col1 column NULL, and again make it NULL:

ALTER TABLE t1 MODIFY col1 NULL;

ALTER TABLE t1 MODIFY col1 NULL;

An error message is generated:

>> ALTER TABLE t1 MODIFY col1 NULL

Error at line 2

ORA-01451: column to be modified to NULL cannot be modified to NULL

Drop the primary key

Drop the primary key with the following statement:

ALTER TABLE t1 DROP PRIMARY KEY CASCADE;

The CASCADE clause drops any foreign keys that reference the primary key. The table gets altered as shown in Figure 4.

add a column to table 4Figure 4. Primary Key dropped

Add a unique key column

Add a new column c2 and make it a unique key with the UNIQUE clause:

ALTER TABLE t1 ADD c2 int UNIQUE;

As the output in Figure 5 indicates, the unique key gets added.

add a column to table 5Figure 5. Adding a new column as unique key

Add a constraint to a column

A constraint may be added with the ALTER TABLE … ADD CONSTRAINT statement. If the primary key was dropped earlier, add a primary key constraint called pk as follows:

ALTER TABLE t1 ADD CONSTRAINT pk PRIMARY KEY(col1);

The primary key constraint gets added, as shown by output in Figure 6.

add a column to table 6Figure 6. Primary key constraint added

Add a CHECK constraint called check_col1 to check that the col1 value is less than 10:

ALTER TABLE t1 ADD CONSTRAINT check_col1 CHECK(col1 <10);

A constraint may be renamed with the ALTER TABLE … RENAME CONSTRAINT … TO .. statement. As an example, rename the primary key constraint pk to pk_constraint:

ALTER TABLE t1 RENAME CONSTRAINT pk TO pk_constraint;

Add a partition to a column

A table partition is used to partition a column’s data if it could contain too much data to store in one partition. As an example, create a range partitioned table with a partition for the column c1:

CREATE TABLE t1_range_partition(c1 INT)

PARTITION BY RANGE (c1) (

PARTITION p1 VALUES less than(10),  

PARTITION p2 VALUES less than(20),

PARTITION p3 VALUES less than(100));

A new partition may be added with the ALTER TABLE …ADD PARTITION clause:

ALTER TABLE t1_range_partition ADD PARTITION p4 VALUES LESS THAN (200);

Use an index for a unique key column

A new column can be made a unique key using an index with the ALTER TABLE … ENABLE UNIQUE(...) USING INDEX statement. As an example, add a new column c2 and make it a unique key using an index:

ALTER TABLE t1 DROP COLUMN c2;

ALTER TABLE t1 ADD c2 INT;

ALTER TABLE t1 ENABLE UNIQUE(c2) USING INDEX;

Alter a column to a table with data

So far, we have discussed how to add a column to table without any data. Next, we discuss how to add a column to table with data. While some column properties may be changed even with existing column data, other column properties cannot be changed.

As an example, a column name may be changed even if the column has data. To demonstrate, create a new table t1 with a column c1. Add some data, and subsequently change column’s name to c2.

CREATE TABLE t1(c1 VARCHAR2(255));

INSERT INTO t1(c1) VALUES('column1');

ALTER TABLE t1 RENAME COLUMN c1 TO c2;

As the output in Figure 7 indicates, the column name gets changed even though it has data.

add a column to table 7Figure 7. Column name changed

However, a column to modify must be empty to change its datatype. To demonstrate, change a column’s data type from VARCHAR2(255) to INT:

CREATE TABLE t1(c1 VARCHAR2(255));

INSERT INTO t1(c1) VALUES('column1');

ALTER TABLE t1 MODIFY c1 INT;

As the output in Figure 8 indicates, a column must be empty to change its datatype.

add a column to table 8Figure 8. Column must be empty to change irs data type

A column with data may be made a primary key, but if the column contains data that violates the primary key constraint, it cannot be made a primary key. As example, add a value of 1 to two rows of data in column c1.

CREATE TABLE t1(c1 INT);

INSERT INTO t1(c1) VALUES(1);

INSERT INTO t1(c1) VALUES(1);

Next, add a primary key on column c1.

ALTER TABLE t1 ADD PRIMARY KEY(c1);

As the output in Figure 9 indicates, the command fails because a primary key constraint is violated.

add a column to table 9Figure 9. Primary key constraint violated

The NOT NULL clause cannot be specified on a column if the column is already NOT NULL. To demonstrate, make a column NOT NULL when the column is already NOT NULL:

ALTER TABLE t1 ADD c1 INT NOT NULL;

As the output in Figure 10 indicates, the column that is already NOT NULL cannot be made NOT NULL again.

add a column to table 10Figure 10. Column that is already NOT NULL cannot be made NOT NULL again

Add a column to a Temporary table

A new column can be added to a temporary table only if no session is bound to the table. To add a column to a temporary table, a setting in Toad for Oracle needs to be configured, for which select View>Toad Options, as shown in Figure 11.

add a column to table 11

Figure 11. View>Toad Session

Select Transactions in the navigation margin. Select Execute scripts in Toad session if not already selected, as shown in Figure 12. Click on Apply and click on OK.

add a column to table 12Figure 12. Options>Execute scripts in Toad session

Create a GLOBAL TEMPORARY table, and add a column to the table:

CREATE GLOBAL TEMPORARY   TABLE t1_temp(c2 INT);

ALTER TABLE t1_temp ADD   c1 INT DEFAULT 1 NOT NULL PRIMARY KEY;

A new column gets added, as indicated by output in Figure 13.

add a column to table 13Figure 13. New column added to a temporary table

Next, modify the Transactions setting to Execute queries in threads as shown in Figure 14. Click on Apply and click on OK.

add a column to table 14Figure 14. Execute queries in threads

Add some data to the temporary table:

INSERT INTO t1_temp(c1,c2) VALUES(1,2);

Table data gets added as shown in Figure 15.

add a column to table 15Figure 15. Data added

The INSERT statement binds a session to the table. Next, alter the table to add a column:

ALTER TABLE t1_temp ADD c3 INT;

This time, an error message gets generated:

>> ALTER TABLE t1_temp ADD c3 INT

Error at line 1

ORA-14450: attempt to access a transactional temp table already in use

The error message is shown in Figure 16.

add a column to table 16Figure 16. New column does not get added to a temporary table

In this article, we discussed how to add a column to table in Oracle Database.

What’s Next?

Explore and Select a Toad product at https://support.quest.com/download-product-select

  1. Toad for Oracle download Free Trial Version from https://www.quest.com/register/54618/
  1. Buy Online or Request Pricing for Toad for Oracle at https://www.quest.com/products/toad-for-oracle/
  1. Get Product Support for Toad for Oracle at https://support.quest.com/

Have questions about Toad Developer Tools? Click Start Discussion and this blog topic will be transferred to the Toad World Forums.

 

Tags: Toad for Oracle

Deepak Vohra

Written by Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.