Subject Re: [ib-support] Adding a default value for a field
Author Lista de Discução Interbase
I don't think so.

From the Language Reference Manual

Changes a table by adding, dropping, or modifying columns or integrity

Available in SQL, DSQL, and isql.

Syntax ALTER TABLE table <operation> [, <operation> …];
<operation> = {ADD <col_def>
| ADD <tconstraint>
| ALTER [COLUMN] column_name <alt_col_clause>
| DROP col
| DROP CONSTRAINT constraint}
<alt_col_clause> = {TO new_col_name
| TYPE new_col_datatype
| POSITION new_col_position}
<col_def> = col {< datatype> | COMPUTED [BY] (< expr>) | domain}
[DEFAULT { literal | NULL | USER}]
[ <col_constraint>]
[COLLATE collation]

the <alt_col_clause> permits only to change type, position or to rename a
column. So I think the best way is to create a dummy column, copy the data
to this column, drop the column, recreate it with the default clause,
update back the values. Something like this just typed not tested for
syntax correction.

Alter table atable add dummycol char(1)
update atable set dummycol = IS_DELETED where IS_DELETED is not null
alter table atable drop IS_DELETED;
Alter table atable add IS_DELETED char(1) default 'F'
update atable set IS_DELETED = dummycol where dummy_col is not null
update atable set IS_DELETED = 'F' where IS_DELETED is null
alter table atable drop dummycol;

BUT if you use IS_DELETED in views, you will have to drop those views and
recreate after the column change, it's a bit of work if you have a lot of

The FB gurus could tell you if it's possible to do it in the "hack way",
changing the system tables.


Alexandre Benson Smith
THOR Software e Comercial Ltda.
Santo Andre - SP - Brazil

At 17:08 21/03/02 +0000, you wrote:
>What I was actually hoping for was to alter the table in the Firebird
>database so that the Default value property of the field in the database is
>set to the appropriate value.
>How do I do that? (Looking back my original question wasn't very clear.)