Oracle alter table add column example

Oracle Tips by Burleson Consulting

We have "alter table" syntax from Oracle to add data columns in-place in this form:

alter table
   table_name
add
   (
   column1_name column1_datatype column1_constraint,  
   column2_name column2_datatype column2_constraint,
   column3_name column3_datatype column3_constraint
   );

Here are some examples of Oracle "alter table" syntax to add data columns.

alter table
   cust_table
add
   cust_sex  varchar2(1) NOT NULL;

Her is an example of Oracle "alter table" syntax to add multiple data columns.

ALTER TABLE 
   cust_table 
ADD 
   (
      cust_sex             char(1) NOT NULL,
      cust_credit_rating   number
   );
 

Sometimes, we find that a piece of data that we did not maintain becomes important, and we need to add a new table column to the database. We can add a table to hold the new data or add it to our current schema by adding a column to a current table.

For example, we discover that we need to keep a record of the last date that each author published and what they published. We need to add two columns to the author table,author_last_published (a date) and author_item_published (a varchar2(40)). To do this, we use the ALTER TABLE ADD command.

SQL> alter table author add (author_last_published date);

Table altered.

SQL> alter table author add (author_item_published varchar2(40));

Table altered.

SQL> desc author
Name Null? Type
------------------------------------- -------- ------------------AUTHOR_KEY VARCHAR2(11)
. . . 
AUTHOR_ITEM_PUBLISHED VARCHAR2(40)

Notice that the new columns are at the end of the AUTHOR table. All current rows in the table now contain NULLs for the new columns.

I added each column separately, but you can add as many columns as needed in one command by separating them with commas.

alter table 
   author 
add 
   (author_last_published date,
    author_item_published varchar2(40));

If I define a default value for the new columns, all the current columns will have the default value. (I dropped and recreated the original author table.)

SQL> alter table author add (
2 author_last_published date default SYSDATE, 
3 author_item_published varchar2(40) 
4 default 'Magazine Article' not null
5 );

Table altered.