Re: default constraint updating existing rows
Date: Tue, 16 Dec 2008 20:34:05 +0100
Message-Id: <C30CC17F-0357-42AA-AE85-BAE1AFA3BA20@gmail.com>
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.comReceived on Tue Dec 16 2008 - 13:34:05 CST
> 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
-- http://www.freelists.org/webpage/oracle-l
- application/pkcs7-signature attachment: smime.p7s