RE: default constraint updating existing rows
- From: "Harvinder Singh" <Harvinder_Singh@xxxxxxxxx>
- To: "Hostetter, Jay M" <JHostetter@xxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Dec 2008 15:24:42 -0500
Thanks a lot, If I specify not null then I can understand Oracle need to
update existing rows but not sure why they are trying to update existing
rows for null column. I tried on other RDBMS and they only update rows
if not null is specified otherwise leave the existing rows null.
-----Original Message-----
From: Hostetter, Jay M [mailto:JHostetter@xxxxxxxxxxxxxxxxxxxx]
Sent: Tuesday, December 16, 2008 1:44 PM
To: Harvinder Singh; oracle-l@xxxxxxxxxxxxx
Subject: RE: default constraint updating existing rows
Try splitting the "alter table" into two different statements.
Alter table test add (b int);
Alter table test modify (b int default 3);
Jay
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Harvinder Singh
Sent: Tuesday, December 16, 2008 1:22 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: default constraint updating existing rows
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
The information in this email is confidential and may be legally
privileged. It is intended solely for the addressee. Access or use by
any other person to this internet email is not authorized and may be
unlawful. If you are not the intended recipient, please delete or
destroy this email. If you do not wish to receive future emails from
this sender, please reply directly to this email requesting you be
removed from any mailing list.
--
//www.freelists.org/webpage/oracle-l
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for
the use of the individual or entity to which they are addressed and may
contain information that is privileged, proprietary and confidential. If
you are not the intended recipient, you may not use, copy or disclose to
anyone the message or any information contained in the message. If you
have received this communication in error, please notify the sender and
delete this e-mail message. The contents do not represent the opinion of
D&E except to the extent that it relates to their official business.
The information in this email is confidential and may be legally privileged. It
is intended solely for the addressee. Access or use by any other person to this
internet email is not authorized and may be unlawful. If you are not the
intended recipient, please delete or destroy this email. If you do not wish to
receive future emails from this sender, please reply directly to this email
requesting you be removed from any mailing list.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
