RE: default constraint updating existing rows

From: McNary, Mark <Mark.McNary_at_umb.com>
Date: Tue, 16 Dec 2008 13:54:35 -0600
Message-ID: <82D79D9524DFE84E9D05EFFB7472E1C10A6811A7@x6008a.umb.corp.umb.com>

If you don't want the default value in all existing rows, then you need to update existing rows after adding the column and before adding the default value.

Thanx,

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
-Mark McNary
-SENIOR DATABASE ADMINISTRATOR
UMB Bank, n.a.
1008 Oak St.
MS1170304
Kansas City MO 64106

(816)860-1934(W) 
(816)213-3125(M) 
(816) 860-1774(F)

mark.mcnary_at_umb.com
http://www.umb.com
"We are the music makers. We are the dreamers of dreams" ~Willy Wonka
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hostetter, Jay M Sent: Tuesday, December 16, 2008 12:44 PM To: Harvinder_Singh_at_picis.com; oracle-l_at_freelists.org 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Harvinder Singh Sent: Tuesday, December 16, 2008 1:22 PM To: oracle-l_at_freelists.org
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.

--
http://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.
--
http://www.freelists.org/webpage/oracle-l



------------------------------------------------------------------------------
NOTICE:  This electronic mail message and any attached files are confidential.  The information is exclusively for the use of the individual or entity intended as the recipient.  If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited.  If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

==============================================================================

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 16 2008 - 13:54:35 CST