Skip to content

Oracle ALTER TABLE ADD Column By Examples

  • by

ALTER TABLE ADD COLUMN

In this post, I'll show you some practical examples of adding a column to table from the simplest to the complicated. Examples may vary in details, but the base clause ALTER TABLE ADD column is always the same.

First of all, we create a table for testing.

SQL> create table t1 (c1 number);

Table created.

As you can see, there should be at least one column in CREATE TABLE statement.

Let's see some useful examples.

  1. Basic Form
  2. Default Value
  3. NOT NULL
  4. Default Value + NOT NULL
  5. Multiple Columns
  6. Check Value

Basic Form

The basic form contains an identifier and its data type for the column.

alter table t1 add (c2 varchar2(20));

Default Value

To prevent fields from empty by accident, we can provide a default value for the column.

alter table t1 add (c3 varchar2(20) default 'Apple');

NOT NULL

NOT NULL is a kind of constraint to columns, it does not allow a NULL value to be inserted or updated.

alter table t1 add (c4 varchar2(20) not null);

Default Value + NOT NULL

We combine both features to confine the column, which is a good practice to prevent ORA-01400: cannot insert NULL by providing a default value for NOT NULL column.

alter table t1 add (c5 varchar2(20) default 'Banana' not null);

Multiple Columns

You can add two or more columns at a time in a single statement.

alter table t1 add (c6 varchar2(20) not null, c7 varchar2(20) not null);

Check Value

We add a check constraint to the column to limit the value within a scope. Let's see some variations.

alter table t1 add (c8 number check (c8 < 10)); alter table t1 add (c8 number check (c8 < 10) not null); alter table t1 add (c8 number default 5 check (c8 < 10)); alter table t1 add (c8 number default 5 check (c8 < 10) not null);

To add an unique index on a column, you should use ALTER TABLE ADD constraint statement.

Leave a Reply

Your email address will not be published. Required fields are marked *