 |
|
Oracle Tips by Burleson |
Altering Tables: Add Columns
Once we have our tables, we may need to make some
changes as our database grows and our data changes. If the table is
empty, we normally just drop and recreate it. If the table has data,
then we need to modify the table in place with the ALTER TABLE
command. Note that the ALTER TABLE command is DML and issues an
implicit commit.
Sometimes, we find that a piece of data that we
did not maintain becomes important, and we need to add it 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_LAST_NAME VARCHAR2(40)
AUTHOR_FIRST_NAME VARCHAR2(20)
AUTHOR_PHONE VARCHAR2(12)
AUTHOR_STREET VARCHAR2(40)
AUTHOR_CITY VARCHAR2(20)
AUTHOR_STATE VARCHAR2(2)
AUTHOR_ZIP VARCHAR2(5)
AUTHOR_CONTRACT_NBR NUMBER(5)
AUTHOR_LAST_PUBLISHED DATE
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.
select
author_key,
author_last_published,
author_item_published
from
author;
AUTHOR_KEY
AUTHOR_LA AUTHOR_ITEM_PUBLISHED
----------- --------- ----------------------------------------
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110
10 rows
selected.
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 'Magizine Article' not null
5 );
Table
altered.
select
author_key,
author_last_published,
author_item_published
from
author;
SQL>
AUTHOR_KEY
AUTHOR_LA AUTHOR_ITEM_PUBLISHED
----------- --------- ------------------------------------A101
20-FEB-05 Magazine Article
A102 20-FEB-05 Magazine Article
A103 20-FEB-05 Magazine Article
A104 20-FEB-05 Magazine Article
A105 20-FEB-05 Magazine Article
A106 20-FEB-05 Magazine Article
A107 20-FEB-05 Magazine Article
A108 20-FEB-05 Magazine Article
A109 20-FEB-05 Magazine Article
A110 20-FEB-05 Magazine Article
10 rows
selected.
Notice that the
author_item_published column was created not allowing NULL
values. I can do this because I specified a default value that was
applied to all the current rows before I added the NOT NULL
constraint. If I did not define a default value, the ALTER TABLE
command would have failed.
SQL> alter
table author add (
2 author_last_published date default SYSDATE,
3 author_item_published varchar2(40) not null
4 );
alter table author add (
*
ERROR at line
1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
The above text is
an excerpt from:
Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
by John Garmany
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
