Re: default constraint updating existing rows
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Dec 2008 20:34:05 +0100
Jay is right:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_3001.htm#sthref5082
DEFAULT
Use the DEFAULT clause to specify a default for a new column or a new
default for an existing column. Oracle Database assigns this value to
the column if a subsequent INSERT statement omits a value for the
column. If you are adding a new column to the table and specify the
default value, then the database inserts the default column value into
all rows of the table.
if you are still in 10g and facing locking problems because of these
statements
http://tonguc.wordpress.com/2008/09/28/11g-enhancement-for-alter-table-add-column-functionality/
might give you a reason to go to 11g ;-)
hth
Martin
--
Martin Berger http://berxblog.blogspot.com
Try splitting the "alter table" into two different statements.
Alter table test add (b int);
Alter table test modify (b int default 3);
Jay
...
Hi,
We are adding a column with default constraint and we only want it to
apply to new rows but it is updating existing rows even though column
allows null. I am using the following test:
SQL> create table test (a int);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> insert into test values(2);
1 row created.
SQL> alter table test add b int default 3;
Table altered.
SQL> select * from test;
A B
---------- ----------
1 3
2 3
SQL> desc test
Name Null? Type
----------------------------------------- -------- --------------
A NUMBER(38)
B NUMBER(38)
Is there any way to add new column with default constraint without
updating existing rows?
Thanks
--Harvinder
Other related posts:
