How to add columns with datatype No Null to existing table with data?
Poster on IDS 7.30 couldn't ALTER TABLE to add NOT NULL columns to a table that already contained rows. Replies explained why: existing rows would hold NULLs. The working fix is to add the column with a DEFAULT (e.g. 'add (col2 int default 0 not null)'), then populate the real values and optionally MODIFY to drop the default; alternatively add it nullable, fill it, then apply the NOT NULL constraint. Serial columns needed no default. A follow-up noted the BEFORE <colname> clause can place the new column at the desired position to match another schema.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all, I'm trying to add some new columns with datatype No Null to a table with data in it. What is the better way to do it? I tried with 'alter table table_name add (column_name datatype no null)' but it's not accepted. Thanks in advance for any help. (I'm working on IDS 7.30UC2 Tools 7.20UE2 on UW7.0.1) H.V.
"Huy Vu" <huyv@usa.net> wrote:
>Hi all,
>
>I'm trying to add some new columns with datatype No Null to a table with
>data in it.
>
>What is the better way to do it?
>
>I tried with 'alter table table_name add (column_name datatype no null)' but
>it's not accepted.
>
>Thanks in advance for any help.
>
>(I'm working on IDS 7.30UC2 Tools 7.20UE2 on UW7.0.1)
>
>H.V.
>
>
Use:
alter table onecol add (col2 int default 0 not null)
and worry about inserting the right values into the column later.
when all the right values are inserted the 'default 0'
is removed with
alter table onecol modify (col2 int not null)
not that is matters to let it stay, as you cant insert a null
to take advantage of the default values.
You could also do it the other way around, ie. create it
without NOT NULL, insert the right values, and then
add the restriction with
alter table onecol add (col2 int default 0 not null)
With method one you are absolutely sure noone
inserts new rows with NULLs in them while you
work on putting the right value to the old rows.
//F.E.Theodorsen
Finn E. Theodorsen///theodor@inet.uni2.dk///AtCbM///Legend#60219605
J-M-B Type: INTP. Homepage: http://www.theodor.suite.dk/index.htm
All advertisments sent to the above address will be
treated as requests for computer support, and charged
accordingly. Sending these kind of messages equals an
acceptance of these terms. The minimum fee is $500.
You can't add a new columns with not null, because the existing rows would currently contain nulls at the time of the alter. However, you should be able to do - 1) add the new column 2) populate the column in the existing rows 3) add a not null constraint. Huy Vu wrote: > Hi all, > > I'm trying to add some new columns with datatype No Null to a table with > data in it. > > What is the better way to do it? > > I tried with 'alter table table_name add (column_name datatype no null)' but > it's not accepted. > > Thanks in advance for any help. > > (I'm working on IDS 7.30UC2 Tools 7.20UE2 on UW7.0.1) > > H.V.
Small Typo here... Read 'no null' as 'not null' naturally, I blame my old worn out keyboard. //F.E.Theodorsen Finn E. Theodorsen///theodor@inet.uni2.dk///AtCbM///Legend#60219605 J-M-B Type: INTP. Homepage: http://www.theodor.suite.dk/index.htm All advertisments sent to the above address will be treated as requests for computer support, and charged accordingly. Sending these kind of messages equals an acceptance of these terms. The minimum fee is $500.
Thanks again for all of you.
I successful to add new columns for existing tables for all kind of datatype
as integer,smallint, char*....
For 'Serial' datatype, I don't need to specify 'default' statement to make
it 'not null'. For 'date' datatype, I still don't know the default value for
it.
The only problem after added new colums is the sequence number of the new
columns is not the same with the one of the original database that I want to
copy the schema from.
Thanks again.
H.V
"Finn E. Theodorsen" <theodor@inet.uni2.dk> wrote in message
news:38ef6a16.8947963@news.uni2.dk...
> "Huy Vu" <huyv@usa.net> wrote:
>
> >Hi all,
> >
> >I'm trying to add some new columns with datatype No Null to a table with
> >data in it.
> >
> >What is the better way to do it?
> >
> >I tried with 'alter table table_name add (column_name datatype no null)'
but
> >it's not accepted.
> >
> >Thanks in advance for any help.
> >
> >(I'm working on IDS 7.30UC2 Tools 7.20UE2 on UW7.0.1)
> >
> >H.V.
> >
> >
> Use:
>
> alter table onecol add (col2 int default 0 not null)>
> and worry about inserting the right values into the column later.
> when all the right values are inserted the 'default 0'
>
> is removed with
>
> alter table onecol modify (col2 int not null)>
> not that is matters to let it stay, as you cant insert a null
> to take advantage of the default values.
>
> You could also do it the other way around, ie. create it
> without NOT NULL, insert the right values, and then
> add the restriction with
>
> alter table onecol add (col2 int default 0 not null)>
> With method one you are absolutely sure noone
> inserts new rows with NULLs in them while you
> work on putting the right value to the old rows.
>
> file://F.E.Theodorsen
>
> Finn E. Theodorsen///theodor@inet.uni2.dk///AtCbM///Legend#60219605
> J-M-B Type: INTP. Homepage: http://www.theodor.suite.dk/index.htm
>
> All advertisments sent to the above address will be
> treated as requests for computer support, and charged
> accordingly. Sending these kind of messages equals an
> acceptance of these terms. The minimum fee is $500.
. Finn E. Theodorsen wrote in message <38f25586.418274@news.uni2.dk>... > >Small Typo here... > >Read 'no null' as 'not null' naturally, I blame my old worn >out keyboard. > >//F.E.Theodorsen >Finn E. Theodorsen///theodor@inet.uni2.dk///AtCbM///Legend#60219605 >J-M-B Type: INTP. Homepage: http://www.theodor.suite.dk/index.htm > >All advertisments sent to the above address will be >treated as requests for computer support, and charged >accordingly. Sending these kind of messages equals an >acceptance of these terms. The minimum fee is $500. --------^ Do you make much money from this ;o) -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf
You can use the BEFORE <colname> clause to move the new column to it correct
position in the physical record to match the other schema.
Art S. Kagel
Huy Vu wrote:
> Thanks again for all of you.
>
> I successful to add new columns for existing tables for all kind of datatype
> as integer,smallint, char*....
>
> For 'Serial' datatype, I don't need to specify 'default' statement to make
> it 'not null'. For 'date' datatype, I still don't know the default value for
> it.
>
> The only problem after added new colums is the sequence number of the new
> columns is not the same with the one of the original database that I want to
> copy the schema from.
>
> Thanks again.
>
> H.V
>
> "Finn E. Theodorsen" <theodor@inet.uni2.dk> wrote in message
> news:38ef6a16.8947963@news.uni2.dk...
> > "Huy Vu" <huyv@usa.net> wrote:
> >
> > >Hi all,
> > >
> > >I'm trying to add some new columns with datatype No Null to a table with
> > >data in it.
> > >
> > >What is the better way to do it?
> > >
> > >I tried with 'alter table table_name add (column_name datatype no null)'
> but
> > >it's not accepted.
> > >
> > >Thanks in advance for any help.
> > >
> > >(I'm working on IDS 7.30UC2 Tools 7.20UE2 on UW7.0.1)
> > >
> > >H.V.
> > >
> > >
> > Use:
> >
> > alter table onecol add (col2 int default 0 not null)> >
> > and worry about inserting the right values into the column later.
> > when all the right values are inserted the 'default 0'
> >
> > is removed with
> >
> > alter table onecol modify (col2 int not null)> >
> > not that is matters to let it stay, as you cant insert a null
> > to take advantage of the default values.
> >
> > You could also do it the other way around, ie. create it
> > without NOT NULL, insert the right values, and then
> > add the restriction with
> >
> > alter table onecol add (col2 int default 0 not null)> >
> > With method one you are absolutely sure noone
> > inserts new rows with NULLs in them while you
> > work on putting the right value to the old rows.
> >
> > file://F.E.Theodorsen
> >
> > Finn E. Theodorsen///theodor@inet.uni2.dk///AtCbM///Legend#60219605
> > J-M-B Type: INTP. Homepage: http://www.theodor.suite.dk/index.htm
> >
> > All advertisments sent to the above address will be
> > treated as requests for computer support, and charged
> > accordingly. Sending these kind of messages equals an
> > acceptance of these terms. The minimum fee is $500.