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 ADMINis used in this article. Grant the user privileges to alter any table.

GRANT ALTER ANY TABLE TO ADMIN;

Further, create a new table t1to 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… ADDstatement. Set the column properties in the same command itself. As an example, add column c1of data type INTwith default value of 1. Set the column as non-nullable with the NOT NULLclause. Set the c1column as the primary key with the PRIMARY KEYclause.

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.

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 c1to 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.

Figure 2. Column renamed

Describe the table t1and 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, c2and 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 MODIFYclause in the ALTER TABLEstatement. As example, change the type of col1to 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.

Figure 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 .. MODIFYcommands runs OK:

ALTER TABLE t1 MODIFY col1 NUMBER(10);

ALTER TABLE t1 MODIFY col1 DEFAULT 2;

Next, modify column col1to 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 col1to 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 col1column 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 CASCADEclause drops any foreign keys that reference the primary key. The table gets altered as shown in Figure 4.

Figure 4. Primary Key dropped

Add a unique key column

Add a new column c2and make it a unique key with the UNIQUEclause:

ALTER TABLE t1 ADD c2 int UNIQUE;

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

Figure 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.

Figure 6. Primary key constraint added

Add a CHECKconstraint called check_col1to check that the col1value 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 pkto 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 PARTITIONclause:

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 INDEXstatement. As an example, add a new column c2and 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 tablet1with 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.

Figure 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.

Figure 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 1to 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.

Figure 9. Primary key constraint violated

The NOT NULLclause cannot be specified on a column if the column is already NOT NULL. To demonstrate, make a column NOT NULLwhen 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 NULLcannot be made NOT NULLagain.

Figure 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.

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.

 

Figure 12. Options>Execute scripts in Toad session

Create a GLOBAL TEMPORARYtable, 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.

Figure 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.

Figure 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.

Figure 15. Data added

The INSERTstatement 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.

Figure 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.

Try Toad free for 30 days.  Free 30-day Trial Already in a trial? Talk to sales or buy now online. Already a loyal fan of Toad for Oracle? Renew now.

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 Discussionand this blog topic will be transferred to the Toad World Forums.

Related Links

Blog: https:o-altith-How to alter and drop database objects with Quest® Toad® for Oracle Pro DB Admin 

Blog: How to Create an Oracle Database Sequence: Explained with Examples 

Blog: Supported Oracle 21c new features in Toad© for Oracle 15.1 

Blog: DB performance: 6 benefits from maintaining database health and stability 

Blog: SQL query optimization: Level up your SQL performance tuning 

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.