Alter table with defaults

Toad for Oracle & DevOps Toolkit
1

Hello everyone,

I need to add three columns via an Alter table script and I need default values
on two of the columns. Problem is there is significant data in the tables and I
do not want to add the default values to the existing rows. I understood there
was a keyword that I could use for this. I am not able to find anything. Any
help appreciated

Timothy Bryan
Sr. Software Engineer

OCS Development Team
W M Waste Management
1021 Main Street, Suite 1728 - C
Houston, TX 77002
( (713) 328-7944

2

Evening Timothy,

On 16/02/12 17:03, Bryan, Timothy wrote:

Hello everyone,
I need to add three columns via an Alter table script and I need default
values on two of the columns. Problem is there is significant data in
the tables and I do not want to add the default values to the existing
rows. I understood there was a keyword that I could use for this. I am
not able to find anything. Any help appreciated
Which version of Oracle please? It may be relevant.

--
Cheers,
Norm. [TeamT]

3

Evening Timothy,

Which version of Oracle please? It may be relevant.
Actually I don't think it is, I've checked the 11g SQL reference and
there's nothing that I can find there. 10g is the same.

It looks to me as if adding a column, with a default, causes the default
to be propagated to all existing rows I'm afraid.

What I think you can do is add the columns and afterwards do an alter
table to add in the default. But I haven't tried it (My wife is home and
supper is ready!)

Set up a small test table and try it out with a couple of rows. See how
you get on.

--
Cheers,
Norm. [TeamT]

4

What I think you can do is add the columns

and afterwards do an alter table to add in the default.

Confirmed! This works to leave the value on the existing records as null.

Roger S.

5

Also confirmed, I just tested it. I will need to copy 116 rows in the script to
do a secondary alter command adding the defaults after adding the columns.

Thanks

Best Regards

Timothy Bryan

We are what we repeatedly do. Excellence, therefore, is not an act but a habit.

Aristotle

6

So if you want to add a column, populate all existing rows with the value X, and
then have the value Y as the default for new rows, you do

ALTER TABLE tb ADD (cl DEFAULT ‘X’);

ALTER TABLE tb MODIFY (cl DEFAULT ‘Y’);

Nate Schroeder

Enterprise Services - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. B2SB - Saint Louis, MO - 63167

314-694-2592

7

Looks to me like a very easy solution in that situation, yup!

Roger S.

8

Thanks everyone, I got what I needed and it worked fine.

Best Regards

Timothy Bryan

We are what we repeatedly do. Excellence, therefore, is not an act but a habit.

Aristotle