Oracle ALTER TABLE ADD Column

ALTER TABLE ADD statement is used to add one or more than one columns to a table in Oracle database.

Syntax:

The syntax of Oracle ALTER TABLE ADD statement is as follows:

ALTER TABLE table_name 
ADD column_name data_type constraint;

Here,

table_name – It is the name of existing table in Oracle database.

ADD column_name – Here, we enter the name of new column that we want to add to existing table. We can’t add an column that already exists in table. We can also add multiple columns together by separating them with a comma and including all columns in a parenthesis.

data_type, constraint – Here, we define the data type of the values in column and constraint associated with it. Adding a constraint to column in optional.

Note:

The new column is always appended at the end of table and there is no way of changing its position like we can do in MySQL.

Oracle ALTER TABLE ADD column examples

Let’s create a sample table named workers for these examples:

CREATE TABLE workers(
    worker_id NUMBER PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    joining_date DATE NOT NULL,
    subject_name VARCHAR(30) NOT NULL,
    teacher_id NUMBER NOT NULL,

);

Let’s add a new column to this table,

The following statement adds a new column named birth_date to the members table:

ALTER TABLE workers 
ADD joining_date DATE NOT NULL;

Here, joining_date column has the DATE data type that does not accept NULL values.

We can also use other data and time formats as data types for these columns allowing us to enter these values automatically. Let’s have a look at this example:

ALTER TABLE
    workers ADD(
        created SYSDATE NOT NULL,
        updated TIMESTAMP WITH TIME ZONE NOT NULL
    );

Here, created column used SYSDATE which automatically takes the date from system and similarly TIMESTAMP WITH TIME ZONE works in same way.

Verifying the presence of Column in table

We can query data from user_tab_cols view to verify whether the column already exists in table or not. Here, we will verify the presence of ‘last_name’ column in workers table,

SELECT
    COUNT(*)
FROM
    user_tab_cols
WHERE
    column_name = 'last_name'
    AND table_name = 'workers';

This query helps us in verifying if the column already exists in the table to avoid running into error before adding a new column.

In this article, we learned the usage of ALTER TABLE ADD column to add one or more columns into a table.