Oracle ALTER TABLE ADD Column

Summary: in this tutorial, you will learn how to use the Oracle ALTER TABLE ADD column statement to add one or more columns to a table.

Introduction to Oracle ALTER TABLE ADD column statement #

To add a new column to a table, you use the ALTER TABLE statement as follows:

ALTER TABLE table_name 
ADD column_name data_type constraint;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table to which you want to add the new column in the ALTER TABLE clause.
• Second, provide the new column name, data type, and its constraint.

Note that you cannot add a column that already exists in the table; trying to do so will cause an error.

Additionally, the ALTER TABLE ADD column statement adds a new column at the end of the table. Oracle provides no direct way to allow you to specify the position of the new column like other database systems such as MySQL.

If you want to add more than one column at once, you can use the following syntax:

ALTER TABLE table_name 
ADD (
    column_1 datatype constraint,
    column_2 datatype constraint,
    ...
);Code language: SQL (Structured Query Language) (sql)

In this syntax, you provide column list inside parentheses after the ADD keyword.

Oracle ALTER TABLE ADD column examples #

First, create a table calledmembers:

CREATE TABLE delivery_orders(
    delivery_id NUMBER PRIMARY KEY,
    customer_id NUMBER NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, add a new column called delivery_date to the delivery_orders table:

ALTER TABLE delivery_orders
ADD delivery_date DATE NOT NULL;Code language: SQL (Structured Query Language) (sql)

In this example, the delivery_date column is a DATE column that does not accept null.

Suppose, you want to record the time at which a row is created and updated. To do so, you need to add two columns created_at and updated_at.

Third, add two new columns to the delivery_orders table:

ALTER TABLE
    delivery_orders ADD(
        created_at TIMESTAMP WITH TIME ZONE NOT NULL,
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL
    );Code language: SQL (Structured Query Language) (sql)

The data types of the created_at and updated_at columns are TIMESTAMP WITH TIME ZONE. These columns do not accept NULL.

To check whether a column exists in a table, you query the data from the user_tab_cols view.

For example, the following statement checks whether the delivery_orders table has the customer_id column.

SELECT
    COUNT(*)
FROM
    user_tab_cols
WHERE
    column_name = 'CUSTOMER_ID'
    AND table_name = 'DELIVERY_ORDERS';Code language: SQL (Structured Query Language) (sql)

This query comes in handy when you want to check whether a column exists in a table before adding it.

For example, the following PL/SQL block checks whether the delivery_orders table has status column before adding it:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  v_col_exists NUMBER;
BEGIN
  SELECT count(*) INTO v_col_exists
    FROM user_tab_cols
    WHERE column_name = 'STATUS'
      AND table_name = 'DELIVERY_ORDERS';

   IF (v_col_exists = 0) THEN
      EXECUTE IMMEDIATE 'ALTER TABLE delivery_orders ADD status NUMBER';
   ELSE
    DBMS_OUTPUT.PUT_LINE('The delivery_orders table already has the column status.');
  END IF;
END;
/Code language: SQL (Structured Query Language) (sql)

If you execute the block for the first time, the effective_date column is appended at the end of the members table.

However, once you execute it for the second time, you will see the following message which is what we expected:

The delivery_orders table already has the column status.Code language: SQL (Structured Query Language) (sql)

Adding non-nullable columns to a table #

To add a column that does not accept NULL to a table, you follow these steps:

• First, add a nullable column to the table.
• Second, change the column values of existing rows to non-null values.
• Third, modify the column by adding the NOT NULL constraint.

For example:

First, create a new table called billings:

CREATE TABLE billings(
   id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   billing_date DATE NOT NULL,
   customer_id NUMBER NOT NULL   
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, insert two rows into the billings table:

INSERT INTO billings(billing_date, customer_id)
VALUES(TO_DATE('2025-05-05', 'YYYY-MM-DD'), 1);

INSERT INTO billings(billing_date, customer_id)
VALUES(TO_DATE('2025-05-05', 'YYYY-MM-DD'), 2);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, retrieve data from the billings table:

SELECT * FROM billings;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Suppose you want to add a non-nullable column called status to the billings table.

Fourth, add the status column to the billings table but set it as nullable:

ALTER TABLE billings
ADD status NUMBER;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The status column will have NULLs.

Fifth, update the values in the status column to the ones you want. In practice, you may need to write a program or script to update status for each billing.

To make it simple, we’ll update the values in the status columns to 1:

UPDATE billings
SET status = 1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Sixth, add the NOT NULL constraint to the status column:

ALTER TABLE billings
MODIFY status NUMBER NOT NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, show the table structure of the billings table to verify:

DESC billings;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

Name         Null?    Type   
------------ -------- ------ 
ID           NOT NULL NUMBER 
BILLING_DATE NOT NULL DATE   
CUSTOMER_ID  NOT NULL NUMBER 
STATUS       NOT NULL NUMBERCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The status column has the NOT NULL constraint.

Summary #

• Use the Oracle ALTER TABLE ADD column statement to add one or more columns to an existing table.

Quiz #