SQL Query - Alter table for default values.
Informix Dynamic Server 7.2 on solaris What is the sql syntax for changing the default value on an existing field in a table. e..g in table x field z was given a default of ' ' (blank space) on creation but now want to alter the table so it has a default of '0' Are there any caveats with doing this ? TIA, a.
ALTER TABLE x MODIFY (z DATATYPE DEFAULT "0");
In other words, write your ALTER TABLE command to MODIFY that column and
include in the MODIFY the datatype (i.e. CHAR(5) or whatever), new DEFAULT,
and any other constraints included in the creation of that column. This is
basically re-defining that column. If you do not specify the other
constraints (if any), you will lose them (but your new default will happily
be there...).
This will leave the values in z in the existing rows as they are and use the
new default in all future INSERT's where a value for z is not specified.
Hal Maner
M Systems International, Inc.
www.msystemsintl.com
Stephens, Alan <alans@credo.ie> wrote in message
news:8427475C51D5D211A0DC00062B000D8F138EA3@tampa.credo.ie...
>
> Informix Dynamic Server 7.2 on solaris
>
> What is the sql syntax for changing the default value on an existing
> field in a table.
>
> e..g in table x field z was given a default of ' ' (blank space) on
> creation
> but now want to alter the table so it has a default of '0'
>
> Are there any caveats with doing this ?
>
> TIA,
> a.
>
"Stephens, Alan" wrote: > Informix Dynamic Server 7.2 on solaris > > What is the sql syntax for changing the default value on an existing > field in a table. > > e..g in table x field z was given a default of ' ' (blank space) on > creation > but now want to alter the table so it has a default of '0' > Too obvious: Alter table <tablename> modify <colname> <coltype> DEFAULT '0'; > > Are there any caveats with doing this ? You will have to manually update any rows inserted with the previous default to the new value if that is what you want. Art S. Kagel