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.
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.
Later on, you might consider to drop unused columns by ALTER TABLE.