Oracle Add a Column to Table

In this Oracle tutorial, we will learn how to add a column to a table in oracle 19c and 21c. Moreover, we will also cover these topics.

• Oracle how to add column to a table
• Oracle add a column to a table with default value
• Oracle how to add column to partitioned table
• Add a column to a table in oracle at specific position
• Oracle how to add not null column to existing table
• Oracle add column to table in the middle
• Oracle add column to table with comment
• Oracle add column to table foreign key
• Oracle add column to table nullable
• Oracle SQL add column from another table

Oracle how to add column to a table

While working on a database you received an email from the client to add a new column in the particular table. What questions you should probe and how to add a column to an existing table? All these things will be covered in this section.

Since the table already exists that means using Alter Table statement you can add a new column to that table.

Below is the syntax for using Alter Table statement with Add clause:

ALTER TABLE <table_name>
ADD <column_name> <data_type> [constraint];

Here are the top 3 things you should know before adding a new column to an existing table:

• Column name and Data type: Please be informed about the column name and its data type. If data type is not shared by the client then ask questions related to the usage of that column and assign data type accordingly.
• Constraint Requirement: Check if there is any constraint reuirement for that column for example, primary key, foreing key, not null, unique, etc.
• Default values: New column will have any existing default values or it will be null at first. If null, then make sure constraints like primary key and not null are not applied on the column.

The below table contains information about the Harry Porter movie that was shot in the united kingdom. We will add a new column Box Office in this table.

  MOVIE_ID           MOVIE_TITLE                        RELEASE_YEAR
---------- -------------------------------------------- ------------
      1      Harry Potter and the Philosopher's Stone       2001
      2      Harry Potter and the Chamber of Secrets        2002
      3      Harry Potter and the Prisoner of Azkaba        2004
      4      Harry Potter and the Goblet of Fire            2005

Script:

Below is the oracle script to create a table and insert a few records in the table. The next oracle script will show how to add a column in the table in the oracle database.

-- CREATE TABLE
CREATE TABLE HP_MOVIES(
    MOVIE_ID INTEGER,
    MOVIE_TITLE VARCHAR2(100),
    RELEASE_YEAR INTEGER
);

-- VIEW TABLE STRUCTURE
DESC HP_MOVIES;

-- INSERT DATA
INSERT INTO HP_MOVIES
VALUES(
    1,
    Q'(Harry Potter and the Philosopher's Stone)',
    2001
);

INSERT INTO HP_MOVIES
VALUES(
    2,
    Q'(Harry Potter and the Chamber of Secrets)',
    2002
);

INSERT INTO HP_MOVIES
VALUES(
    3,
    Q'(Harry Potter and the Prisoner of Azkaban)',
    2004
);

INSERT INTO HP_MOVIES
VALUES(
    4,
    Q'(Harry Potter and the Goblet of Fire)',
    2005
);


-- COMMIT CHANGES
COMMIT;

-- VIEW TABLE DATA
SELECT * FROM HP_MOVIES;

The below script will add a new column “Box Office” in the table in the oracle database.

-- VIEW TABLE STRUCTURE
DESC HP_MOVIES;

-- ADD NEW COLUMN
ALTER TABLE HP_MOVIES
ADD "BOX OFFICE" VARCHAR2(100);

-- VIEW TABLE STRUCTURE
DESC HP_MOVIES;

Output:

In the below output, the yellow highlighted text shows the script to add a new column in the oracle database. The red marked area shows a new column added to the hp_movies table.

Read: How to Get List all Tables in Oracle

Oracle add a column to a table with default value

If you are adding a column to a table in the oracle database with a primary key or Not null constraint then you will get an error because the new column will have null values for already existing rows.

To fix this error, add a column with the default value in the oracle database. A default value can be fixed or variable.

In the above section, we have learned how to add a column in the oracle database. We have added a new column ‘Box Office‘ in the ‘hp_movies‘ table.

But the new column added doesn’t have any values in it. The below image shows the viewing of data inside the hp_movies table. Column Box Office doesn’t have any values.

In this section, we will re-create the hp_movies table with the default value of $1,000,000,000 assuming based upon the popularity of the movie.

In the below script we have added a column to a table with default values in the oracle database.

ALTER TABLE HP_MOVIES
ADD "BOX OFFICE" VARCHAR2(100)
DEFAULT '$1,000,000,000';

Below is the output of the above script, Box Office column is added with default values in each row.

Read: Alter Table Add Column Oracle

Oracle how to add column to partitioned table

Full table scan and indexing are inefficient when the size of data reaches billions of records. At that time table partitioning can improve the performance while handling huge data.

Any table having a size of more than 2GB is advised to have a partitioned table in the oracle database. Huge data is divided into chunks that will be identified by a region.

For example, there is a country happiness database, if you want to search for several people happy in a specific region a full scan will run and it will take time depending upon the size of the data.

Here is the example, to create a partitioned table, using the id column of GDP table as the partitioning key, into 5 partitions.

ALTER TABLE GDP
    PARTITION BY HASH(id)
    PARTITIONS 5;

Read: Oracle add primary key

Add a column to a table in oracle at specific position

Oracle always places the new column at the end. Like other database languages, you cannot control the position of a new column in oracle database 19c & 21c.

But, there is a workaround for this, and in this section, we will talk about that with the help of an example. We will continue using our Harry Porter Movie example in this section as well.

Below are the steps for the workaround to add a column to a table in oracle at a specific position:

• Add new column of your choice in the exsiting table using Alter Table statement.
• Create a new table using As Select statement and provide the column names in the sequence of your choice.

Example:

In the below example, we have added a new column “Run Time” to the HP_Movies table using Alter Table statement. And then created a new table “Harry_Movies” from the HP_Movies table using As Select statement.

-- ADD NEW COLUMN
ALTER TABLE HP_MOVIES
ADD "RUN TIME" INTEGER;

-- VIEW TABLE STRUCTURE
DESC HP_MOVIES;

-- CREATE TABLE WITH COLUMNS AT SPECIFIC POSITION
CREATE TABLE HARRY_MOVIES 
AS (SELECT MOVIE_ID, MOVIE_TITLE, RELEASE_YEAR, "RUN TIME", "BOX OFFICE"
    FROM HP_MOVIES);

-- VIEW TABLE STRUCTURE
DESC HARRY_MOVIES;

Output:

In the below output, the table structure is described. At first, the parent table HP_MOVIES structure is displayed in which the newly added column “RUN TIME” is at the last position.

In the second structure, the Harry_Movies table is shown which is created using the parent table. In this table “RUN TIME” column is positioned at the position of choice.

Read: Oracle Add Foreign Key

Oracle how to add not null column to existing table

Using Alter Table statement, a new column can be added to an existing table in the oracle database. The newly added column doesn’t have values (if not specified at the time of creation).

So if you are trying to add a column with Not null constraint and you have not specified default value then oracle won’t allow you to create a table as this violates the Not Null rule.

The easiest fix to this problem is to add a column with some default value, you can also use sequence for this and then assign Not Null constraint to that column.

Below is the syntax of adding a column to an existing table, not null constraint:

ALTER TABLE <table_name>
ADD <col_name> [data type] default <value> NOT NULL;

Let’s use this syntax, and add a new column ‘ Best Chapter’ with Not Null to the existing table HP_MOVIES.

ALTER TABLE HP_MOVIES
ADD "BEST CHAPTER" VARCHAR2(100) 
DEFAULT 'Not Really Going Home' NOT NULL;

Output:

In the below output, a new column is added with default values. You can check the data type using desc hp_movies.

Read: Oracle to_char Date Format

Oracle add column to table in the middle

In oracle new column can be added at the end only. Oracle believes that column position doesn’t matter as data can be handled with a select query.

You can add a column to a table in the middle in 3 ways and all of these are just workarounds (may or may not work for your requirements)

1. Create a view of the table and position the column in the middle
2. Using DBMS_redefinition package in PLSQL you can rebuild the table manually.
3. Using As Select statement create a new table using parent table and keep the preferred column in the middle in oracle database.

Read the above section for an example – Add a column to a table in oracle at a specific position.

Read: Oracle Database vs MySQL Database

Oracle add column to table with comment

Comments are used to leave a message for the next developer in charge. It could be an instruction, message, description of variables & algorithm, or todo.

Comments can be added inside an oracle query using double dashes (--) for single line and /* */ for multiline comments.

Adding a column and comment are two different activities that can’t be performed with a single command in oracle database 19c and 21c. In this section, we will learn how to add comments on the column of the table.

Below is the syntax to create and view the comment in the oracle database:

-- CREATE COMMENT
COMMENT ON COLUMN <table_name>.<column_name>
IS 'Write comment here!'; 

-- VIEW COMMENT
SELECT * FROM USER_COL_COMMENTS
WHERE TABLE_NAME = '<table_name>;

Example:

Below is the table that we are going to use in this example. In this table, we have added comments on the country name column.

CREATE TABLE COUNTRIES(
    COUNTRY_NAME VARCHAR2(100),
    COUNTRY_CODE VARCHAR2(10),
    CONSTRAINT PK_COUNTRIES PRIMARY KEY(COUNTRY_CODE)
);

Using this script, you can add, view, and remove comments from a column in oracle database 19c & 21c. Please read the inline comments for more detail.

-- ADD COMMENT
COMMENT ON COLUMN COUNTRIES.COUNTRY_NAME
IS 'UNITED STATES OF AMERICA';

-- VIEW COMMENT
SELECT * FROM USER_COL_COMMENTS
WHERE TABLE_NAME ='COUNTRIES';

-- REMOVE COMMENT
COMMENT ON COLUMN COUNTRIES.COUNTRY_NAME
IS '';

Read: Number Datatype in Oracle

Oracle add column to table foreign key

A foreign key is a column(s) in a table that creates a relationship between data in two or more tables. Read our dedicated blog on Foreign key – Oracle Add Foreign Key

In this section, we will show how to add a foreign key to an existing table in oracle databases 19c & 21c.

• When primary key of one table is used in other table then it is called foreign key of that table.
• Foreign key can have null values.
• Using Alter table statement we can add foreign key to the existing table in oracle database.

Below is the syntax of adding a foreign key to an existing table in oracle:

ALTER TABLE <table_name>
ADD CONSTRAINT FOREIGN KEY (col_name)
REFERENCES <parent_table>(col_name);

Example:

In the below example, we have two tables: Countries & GDP. Countries table has two columns – country name and country code. GDP table has three columns – ID, Country code, and GDP(2018).

• In country table, country code is the primary key and is used in GDP table as a foreign key.
• Using Alter Table statement we have added foreign key on GDP table.

COUNTRY_NAME                     COUNTRY_CODE
---------------------------     -------------
UNITED STATES OF AMERICA           USA
UNITED KINGDOM                     GBR
CANADA                             CAN
AUSTRALIA                          AUS
GERMANY                            DEU

        ID   COUNTRY_CODE      GDP(2018)
    ------   ----------     ------------
         1     USA            53074.5401
         2     GBR            45973.5735
         3     CAN             48130.256
         4     AUS            51663.3651
         5     DEU            53074.5401

Using the below script, foreign key constraints can be added to the GDP column in the oracle database. The foreign key is assigned to the Country_code column. On Delete Cascade option will delete the column when it is deleted from the parent column.

ALTER TABLE GDP
ADD CONSTRAINT FK_GDP FOREIGN KEY (COUNTRY_CODE)
REFERENCES COUNTRIES(COUNTRY_CODE)
ON DELETE CASCADE;

Read: How to create a database in Oracle

Oracle add column to table nullable

If the user is allowed to leave the column empty and the column shows null values in the database that means the column is nullable.

By default, all the columns are nullable until constraints like the Primary key and Not Null are added to it.

Using Alter Table Statement you can modify the Not nullable column to nullable in oracle database 19c & 21c.

Syntax:

-- Create nullable columns
CREATE TABLE <table_name>(
col <data type>,
col <data type>);

-- Drop columns to nullable 
ALTER TABLE <table_name>
DROP CONSTRAINT [PRIMARY KEY NAME | NOT NULL]

Read: How to Check Oracle Database Version

Oracle SQL add column from another table

Using As Select in oracle database 19c & 21c you can add a column from another table. In the above section – Add a column to a table in oracle at a specific position, we have demonstrated an example to add a column from another table.

As Select statement allows to add multiple columns from multiple tables even from different schemas.

Below is the syntax for oracle SQL add a column from another table using As Select statement:

CREATE TABLE <table_name>
AS (SELECT <schema>.<col_name>, <schema>.<col_name>
    FROM <schema>.<table_name>);

Read our blog – How to create a table in Oracle, Here we have covered examples and more details about how to add columns from another table in the oracle database 19c and 21c.

You may also like to read the following Oracle tutorials.

• How to backup table in Oracle
• Oracle Database Datatype
• Connect Excel to Oracle Database
• How to Fetch Data from Oracle in Python
• Connect to Oracle Database using Python

In this tutorial, we have learned how to add a column to a table in oracle databases 19c and 21c. Also, we have covered these topics.

• Oracle how to add column to a table
• Oracle add a column to a table with default value
• Oracle how to add column to partitioned table
• Add a column to a table in oracle at specific position
• Oracle how to add not null column to existing table
• Oracle add column to table in the middle
• Oracle add column to table with comment
• Oracle add column to table foreign key
• Oracle add column to table nullable
• Oracle SQL add column from another table