Alter Table Add Column Oracle

In this Oracle tutorial, we will cover the use of Alter table add column in Oracle. Here we will learn how to perform alter table columns in the oracle database. Also, we will cover these topics.

• Alter table add column in oracle
• Alter table add column oracle default value
• Alter table add column oracle default value null
• Alter table add column oracle varchar2
• Alter table add column oracle nullable
• Alter table add column oracle not null
• Alter table add column oracle number
• Alter table add column oracle after
• Alter table add column oracle date
• Alter table add column oracle default value timestamp

Note: All the dataset used in this tutorial is from Kaggle and we do not guarantee accuracy. The dataset is used to explain the concepts well.

Alter table add column in oracle

In this section, we will learn how to add column(s) in the oracle database using alter table command.

• In Oracle, ALTER TABLE statement is used to add, modify, delete or drop columns from the table.
• Alter table statement is also used for changing the table name in oracle database.
• Here is the syntax for using Alter table command in oracle database.

ALTER TABLE <table_name>
ADD <column_name> <data-type> <other-definiton>;

Example:

In our example, we have a table: life_expectancy within the schema: c##sqlserverguides. We will add a new column: Population using Alter table statement.

COUNTRY_NAME               YEAR LIFEEXPECTANCY
-------------------- ---------- --------------
Australia                  2016      82.519997
Canada                     2016      81.849998
Germany                    2016          80.93
Mexico                     2016      76.379997
United Kingdom             2016      80.900002
United States              2016      78.860001

Source Code:

Here is the source code of an example in which we are going to create a new column for the life_expectancy table created inside the user c##sqlserverguides.

Alter table c##sqlserverguides.life_expectancy
Add Population NUMBER 

commit

Implementation on SQL developer tool

Output:

This output is divided into two parts, in the first part, we have displayed the original table while in the second parted we have added a new column ‘Population’ using the alter table statement.

Also, read: How to Check Oracle Database Version

Alter Table Add Column Oracle Default Value

In this section, we will learn how to perform alter table add column oracle default value.

• We will learn how to give a default value to the column in oracle database using alter table statement.
• Suppose, you already have a table now you are adding a new column to it. If you won’t assign a default value to it then it will have empty space.
• In our previous section, we have added a column ‘Popluation’ and we didn’t provided any default value to it. As a result of which the entire column was empty.
• In our example, we will add a new column Population with the default value as 100000.

Example:

In our example, we will use the below table and add a new column Population with a default value of 100000.

COUNTRY_NAME               YEAR LIFEEXPECTANCY
-------------------- ---------- --------------
Australia                  2016      82.519997
Canada                     2016      81.849998
Germany                    2016          80.93
Mexico                     2016      76.379997
United Kingdom             2016      80.900002
United States              2016      78.860001

Source Code:

Here is the source code of an example in which we are going to create a new column for the life_expectancy table created inside the user c##sqlserverguides. The new column’s name is Population and it has a default value of 100000.

alter table c##sqlserverguides.life_expectancy
Add population Number default 100000;

COMMIT

Implementation on SQL developer tool

Output:

This output is divided into two parts, in the first part we have not used the default value due to which the new column has an empty value. Whereas in the second part default value is assigned to a new column population.

Read: How to Get List all Tables in Oracle Database

Alter table add column oracle default value null

In the previous section, we have discussed the default values in alter table. In this section, we will perform alter table add column oracle default value null.

• Null in oracle database refers to nothing or an empty value.
• Using alter table command we can add a new column and at the end of that column we can mention the default value as null.
• Oracle automatically sets the default value to null incase user has not specified any.

Example:

In our example, we will use the below table and add a new column Population with a default value that is null.

COUNTRY_NAME               YEAR LIFEEXPECTANCY
-------------------- ---------- --------------
Australia                  2016      82.519997
Canada                     2016      81.849998
Germany                    2016          80.93
Mexico                     2016      76.379997
United Kingdom             2016      80.900002
United States              2016      78.860001

Source Code:

Here is the source code of an example in which we are going to create a new column for the life_expectancy table created inside the user c##sqlserverguides. The new column’s name is Population and it has a default value that is null.

alter table c##sqlserverguides.life_expectancy
Add population Number default null;

COMMIT

Implementation o SQL developer tool

Output:

In this output, since we set the default value as null that is why the result shown is empty.

Read: Connect Excel to Oracle Database

Alter table add column oracle varchar2

In this section, we will learn to implement alter table add column oracle varcahr2.

• We will add a new column with the name status in life_expectancy table. This column will have data type as varchar2 & will tell whether the country is developer or developing.
• Varchar2 is a data type in oracle used to store strings and character(s) in oracle database.
• While implementing varchar2 data type you may get the following error:

ERROR at line 2:
ORA-00906: missing left parenthesis
00906. 00000 -  "missing left parenthesis"

• This error can be fixed by adding a size or limitation to the varchar2. Use varchar2(20), here 20 is the imitation set for varchar data type.

Example:

In our example, we will demonstrate how to alter the table add a column with varchar2 data type in the oracle database. In the below code, c##sqlserverguides in the schema name, and life_expectancy is the table name.

Alter table c##sqlserverguides.life_expectancy
Add status VARCHAR2(20) default 'Developed';

COMMIT

Implementation on SQL Developer tool

Output:

In this output, a new column ‘Status’ is added and it has varchar2 datatype.

Read: How to Fetch Data from Oracle Database in Python

Alter table add column oracle nullable

In this section, we will learn to alter the table add column oracle nullable.

• While creating a table we have added a constraint not null. Which means that columns can’t be left empty.
• Suppose the business requirement changed in future not that column can be left empty. That thing we are going to learn in this section.
• Using Alter table keyword we can change the constraint type of a column(s).

Example:

In our example, we are going to change the constraint type for the YEAR column from Not Null to Null. This means a user can now leave the column empty. Below is the table description that we are going to use.

 Name                                      Null?    Type
----------------------------------------- -------- -------------------
 COUNTRY_NAME                              NOT NULL VARCHAR2(20)
 YEAR                                      NOT NULL NUMBER(38)
 LIFEEXPECTANCY                            NOT NULL FLOAT(126)
 STATUS                                             VARCHAR2(20)

We tried passing the null or empty value in the column with not null constraint. And it threw this error:

One error saving changes to table "C##SQLSERVERGUIDES"."LIFE_EXPECTANCY":
Row 7: ORA-01400: cannot insert NULL into ("C##SQLSERVERGUIDES"."LIFE_EXPECTANCY"."YEAR")
ORA-06512: at line 1

Moving forward we will convert the YEAR column to nullable using alter table statement in the oracle database.

Source Code:

Here is the source code, to convert the oracle database column constraint to Nullable.

Alter table c##sqlserverguides.life_expectancy
MODIFY (YEAR NULL);

COMMIT

Implementation on SQL developer tool

Output:

In this output, we are going to perform the same activity that we did at the start of the example. We will try to put the null value inside the YEAR column.

Read: Connect to Oracle Database using Python

Alter table add column oracle not null

In this section, we will learn about alter table add column oracle not null.

• Not Null is the constraint in oracle datbase that means the that column can’t be left empty.
• Not Null constraint is applied on the columns which are mandatory to fill. for example, name, mobile number, email, etc are some of the important columns.
• If these columns are left empty then it will have a great impact on the database.
• Any column with Not Null constraint if left empty will throw an error.
• Using Alter table statement we can modify the column constraints. Incase you are getting below error while changing the constraints that means there is a null value in the record of that column. Remove that null value and then try again.

Error report -
ORA-02296: cannot enable (C##SQLSERVERGUIDES.) - null values found
02296. 00000 - "cannot enable (%s.%s) - null values found"
*Cause:    an alter table enable constraint failed because the table
           contains values that do not satisfy the constraint.
*Action:   Obvious

• In our example, we will show you convert nullable column to not null in oracle database. you can see in the below image that column Year currently don’t have not null constraint.

Source Code:

In this source code, we have changed the constraint of the Year column from nullable to not null using alter table in the oracle database.

Alter table c##sqlserverguides.life_expectancy
modify (YEAR Not Null);

COMMIT

Here is the implementation on the SQL developer tool

Output:

This output is divided into two parts, the first part shows that there is no constraint applied on the Year column whereas in the second picture we have applied the Not Null constraint using Alter table statement.

Read: Number Datatype in Oracle Database

Alter table add column oracle number

In this section, we will learn how to perform alter table add column oracle number in oracle database.

• We will learn to add new column with data type as Number using Alter Table in oracle database.
• In our example, we will add new column as AdultMortality which will be the probability of dying between 15 and 60 years per 1000 population.

Source code:

In this source code, we have added a new column with the name adultMortality with Number data type.

Alter table c##sqlserverguides.life_expectancy
Add adultMortality NUMBER;

COMMIT

Here is the implementation on the SQL developer tool

Output:

This output shows that the before database doesn’t have an AdultMortality column. But we have added it using Alter table statement in the oracle database.

Read: Oracle Database vs MySQL Database

Alter table add column oracle after

In this section, we will learn about alter the table column oracle after.

• We can add new column in a table using alter table command in oracle database not only that we can also control the position where we want to place the new column.
• If you want to add a new column: ‘Last Name’ then probably you want to add it after the ‘First Name’ column.
• So in this section, we will learn how to add a new column after a specific column.
• In our example, we are following life expectancy table and so far we have this data in our life_expectancy table.

COUNTRY_NAME               YEAR LIFEEXPECTANCY ADULTMORTALITY
-------------------- ---------- -------------- --------------
Australia                  2016      82.519997             59
Canada                     2016      81.849998             64
Germany                    2016          80.93             68
Mexico                     2016      76.379997            122
United Kingdom             2016      80.900002             69
United States              2016      78.860001             13

• Moving forward, we are going to add a new column after LifeExpectancy column using alter table statement in oracle database.

Source Code:

In this source code, we have used the after keyword in Alter table to place the new column at the specific position in the table.

Alter table c##sqlserverguides.life_expectancy
Add status NUMBER After LIFEEXPECTANCY;

COMMIT;

Implementation on SQL developer tool

Output:

In this output, the date column has been added using alter table statement. Since the date column was assigned with the default value as sysdate so it has picked up the system date and published it on the database.

Read: How to create a database in Oracle 19c

Alter table add column oracle date

In this section, we will learn about Alter table add column oracle date in the oracle database.

• We will create a column with date data type in oracle database.
• Using Date data type in oracle database we can insert the record with date. This helps in adding detail to the database only makes easy to fetch data.
• Date can be added to an existing table using alter table comand in oracle database.
• Use sysdate as default value while creating date column. This will automatically assign the current date of the system.

Source Code:

In this source code, using the alter table statement we have added a new column Log_date. This column has default value as sysdate, which means every time the database is being created it will pick up the system date automatically.

ALTER table c##sqlserverguides.life_expectancy
Add Log_date Date default sysdate + 1;

COMMIT;

Implementation on SQL developer tool.

Output:

In this output, the date column has been added using alter table statement. Since the date column was assigned with the default value as sysdate so it has picked up the system date and published it on the database.

Read: Oracle to_char Date Format

Alter table add column oracle default value timestamp

In this section, we will learn about alter table add column oracle default value timestamp.

• We will create a new column in the existing table and will add the timestamp on it.
• Using Alter Table statement in oracle database we can create a new column and for timestamp there are two available options:
  • TIMESTAMP WITH TIME ZONE
  • TIMESTAMP WITH LOCAL TIME ZONE
• TIMESTAMP WITH TIME ZONE will display the date, time and time zone. All the inforamtion will be picked from the system.
• TIMESTAMP WITH LOCAL TIME ZONE will display date and time only. All the information will be fetched from the system.
• To setup the default time stamp use CURRENT_TIMESTAMP as the default. This will fill the timestamp information instead of null.

Source Code:

In this source code, we have created a Local_Log_Time column to demonstrate TIMESTAMP WITH LOCAL TIME ZONE and Log_time column to demonstrate the TIMESTAMP WITH TIME ZONE in the oracle database.

# time zone
ALTER table c##sqlserverguides.life_expectancy
Add Local_Log_Time TIMESTAMP WITH TIME ZONE DEFAULT Current_Timestamp;

COMMIT;


# Local time zone
ALTER table c##sqlserverguides.life_expectancy
Add Local_Log_Time TIMESTAMP WITH LOCAL TIME ZONE DEFAULT Current_Timestamp;

COMMIT;

Output:

In this output, we have added a new column with the name TimeStamp Log. This will keep a record of the current timestamp in the database. Also, since current_timestamp is set to default so instead of displaying null it will show the current date and time.

You may also like to read the following Oracle tutorials.

• Oracle Add Foreign Key

In this tutorial, we have learned how to perform alter table columns in the oracle database. Also, we will cover these topics.

• Alter table add column in oracle
• Alter table add column oracle default value
• Alter table add column oracle default value null
• Alter table add column oracle varchar2
• Alter table add column oracle nullable
• Alter table add column oracle not null
• Alter table add column oracle number
• Alter table add column oracle after
• Alter table add column oracle date
• Alter table add column oracle default value timestamp